CODEWITHSUNDEEP
mysqlregex
Sagar Upadhyaya
Sagar Upadhyaya

Reputation: 35

Mysql regex for finding space separated number 0

I need to find the space separated number '0'. Need to use in my sql query to search for some results. I used [[:<:]]0[[:>:]] but this is returning 0%, 0.5% ,0.00, 1.0 along with actual result like Rs 0

What should I use to only fetch valid results which doesn't include these results like 0% or 0.5 or 1.0

Edited: Solution given by Rahul was correct but I also found alternative solution->

 WHERE column_name REGEXP '([[:blank:]]|^)0([[:blank:]]|$)'

Upvotes: 1

Views: 40

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

You can try it without regex like this:

WHERE CONCAT(' ', mycol, ' ') LIKE '% 0 %'

And if the 0 is at the beginning or end then you can use OR

WHERE CONCAT(' ', mycol, ' ') LIKE '% 0 %' OR
CONCAT('', mycol, ' ') LIKE '%0 %' OR
CONCAT(' ', mycol, '') LIKE '% 0%'

Upvotes: 2

Related Questions