CODEWITHSUNDEEP
mysql
Zeeshan Rang
Zeeshan Rang

Reputation: 19875

Issue using LIKE in a SQL query with special characters in it

SELECT id, absolute_tag, tag FROM c7_storage_tags
WHERE users_id = 1 
      AND LOWER(absolute_tag) LIKE LOWER('@#Pictures#zee\'s fav%')

This is not giving me the correct result, even thou i do have a row in my database with the correct string.

The better solution for me would be to figure out a way so that "@#Pictures#zee\'s fav" string of mine, could be treated as just a string. And I can perform like on it.

How can I fix it.

Thanks in advance.

Zeeshan

Upvotes: 1

Views: 377

Answers (1)

a1ex07
a1ex07

Reputation: 37354

IF you string has a backslash in it, you should escape it twice :

// value absolute_tag == "@#Pictures#zee\'s fav"
select * from test where  `absolute_tag` like '@#Pictures#zee\\\\\'%'

From http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

Your query should look like

SELECT id, absolute_tag, tag FROM c7_storage_tags
WHERE users_id = 1 
AND LOWER(absolute_tag) LIKE LOWER('@#Pictures#zee\\\\\'s fav%')

Your original query was looking for a record with absoulte_tags begins with "@#Pictures#zee's fav"

Upvotes: 3

Related Questions