How to match Underscore in String - MYSQL

Question

if column value is 2_apples or final_chapter how do i match a value that contains an underscore "_" ?

I've tried with:

SELECT DISTINCT brand FROM `items` WHERE brand like '%\\_'
SELECT DISTINCT brand FROM `items` WHERE brand like '_'
SELECT DISTINCT brand FROM `items` WHERE brand like '%_%'
SELECT DISTINCT brand FROM `items` WHERE brand like LIKE '\_'

It either shows all results or none. Any suggestions?

i did but it fetches Sony `Playstation` `Micronet` `Playstation 4` `Gaems` `Sony VR` but not `e_gold` — RanaHaroon, Jul 07 '17 at 13:58
In addition to the answer below and the duplicate link, you could also use `REGEXP` with underscore directly, without the need to escape: `WHERE brand REGEXP '_'` — Tim Biegeleisen, Jul 07 '17 at 13:58

score 20 · Accepted Answer · edited Jul 07 '17 at 14:01

20

Try

WHERE brand LIKE '%\_%'

The underscore is a wildcard for a single character whereas the % is a wildcard for multiple (or none) characters. Put a backslash in front to escape them.

If you don't like escaping things, because it's ugly or scary, then an alternative would be to use the REGEXP operator with underscore directly:

WHERE brand REGEXP '_'

edited Jul 07 '17 at 14:01

Tim Biegeleisen

502,043
27
286
360

answered Jul 07 '17 at 13:55

fancyPants

50,732
33
89
96

How to match Underscore in String - MYSQL

1 Answers1