Mysql LIKE or REGEXP

Question

I am trying to select all the strings has word ABC1 in it. But getting wrong answer with LIKE query. Not sure how to get exact answer.

Below is the string format in a column:

"SomeString ABC1000 Other";

And I don't want to select any String which is in the format like
"itisABC1000 other other"

All strings from a single column, all strings from all the columns, or search every single table and every single column? — easleyfixed
– easleyfixed, Commented May 7, 2024 at 20:28
So you require ABC1 to be at the beginning of a word? Use a regexp, with the [:<:] word beginning pattern. — Barmar
– Barmar, Commented May 7, 2024 at 20:37

nbk · Accepted Answer · 2024-05-07 20:53:58Z

1

with reular expression you can use REGEXP\_LIKE

this oirks for MYsql 8.x

select REGEXP_LIKE("SomeString ABC1000 Other",'ABC1')

REGEXP_LIKE("SomeString ABC1000 Other",'ABC1')
1

fiddle

Your query would wlook like

SELECT yourcolmuns FROM your_table WHERE REGEXP_LIKE(yourlike_column,'ABC1')

for older Versions like 5.7

select "SomeString ABC1000 Other" REGEXP 'ABC1'

"SomeString ABC1000 Other" REGEXP 'ABC1'
1

fiddle

so the query would look like

SELECT yourcolmuns FROM your_table WHERE yourlike_column REGEXP 'ABC1'

edited May 7, 2024 at 20:53

answered May 7, 2024 at 20:36

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yashawant Sawant Over a year ago

I think your answer is right. But regexp_like is an Oracle function. And I am using Mysql. It gave error as FUNCTION mysql.regexp_like does not exist

nbk Over a year ago

this was introduced in MySQL 8.x but i added for 5.7, if you are older you should think about updating the version

Yashawant Sawant · Accepted Answer · 2024-05-07 21:08:03Z

0

Found answer with LIKE

select column
from table
where conditions like "% ABC1%" or conditions like "ABC1%";

Thank you!

answered May 7, 2024 at 21:08

Yashawant Sawant

437 bronze badges

1 Comment

ysth Over a year ago

Often people do something like concat(' ',conditions) like '% ABC1%'

Collectives™ on Stack Overflow

Mysql LIKE or REGEXP

2 Answers 2

this oirks for MYsql 8.x

for older Versions like 5.7

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

this oirks for MYsql 8.x

for older Versions like 5.7

2 Comments

1 Comment

Related