CODEWITHSUNDEEP
mysql
vascowhite
vascowhite

Reputation: 18430

How can I search for a phrase Like O'% in MySql?

I have a MySql database that contains people's names. Of course there are names stored that begin with O'.

If I try to retrieve the name "O'Brien" with sql like this:-

SELECT person_sname
FROM people
WHERE person_sname = 'O\\\'Brien'

Then I get the expected result. However, if I try to retrieve all names beginning with "O'" like this:-

SELECT person_sname
FROM people
WHERE person_sname like 'O\\\'Brien'

Then I get an empty result set. My goal is to search for like "O\\\'".

Is there any explanation of why this is happening? I have searched the MySql manual and can find no explanation of why my search shouldn't work as expected.

Other questions I have found deal with storing the escaped string or with retrieving it using = which works faultlessly, I specifically want to use the like clause on this occassion.

To clarify: I have tried the following also:-

SELECT person_sname
FROM people
WHERE person_sname like 'O\'Brien' escape "\'"

SELECT person_sname
FROM people
WHERE person_sname like 'O\'Brien'

SELECT person_sname
FROM people
WHERE person_sname LIKE 'O\'Brien'
ESCAPE '~'

The string is escaped on insertion to the db usimg mysql_real_escape_string() and is stored as O\'Brien (for example).

Upvotes: 1

Views: 669

Answers (4)

Álvaro González
Álvaro González

Reputation: 146558

You were injecting a literal backslash. This is enough:

SELECT person_sname
FROM people
WHERE person_sname = 'O\'Brien'

To test:

SELECT 'O\\\'Brien', 'O\'Brien'

Update: There's already an accepted answer so I'll just add a couple of clarifications.

First, I had overlooked the fact that stored data was corrupted. Needless to say, that explains why person_sname = 'O\\\'Brien' is true.

Second, the \ character has two usages in MySQL syntax:

  1. It's the character used to compose escape sequences: \n, \t...
  2. It's the default escape char to insert literal % and _ characters in LIKE expressions: foo LIKE '%abc\%def%'

The problem is that inserting a \ symbol in a LIKE expression triggers both behaviours so you actually need to insert four backslashes to get one:

person_sname like 'O\\\\\'Brien'

... unless you change the second meaning with the ESCAPE clause:

person_sname like 'O\\\'Brien' escape '|'

Of course, if data was not corrupted you could simply:

person_sname = 'O\'Brien'
person_sname like 'O\'Brien'

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453648

\ is the default ESCAPE character for LIKE so you need to either escape it again or define a different ESCAPE character.

I wouldn't store names with escape characters like that anyway.

SELECT 
        CASE WHEN name LIKE 'O\\\'Brien' THEN 1 ELSE 0 END,             /*0*/
        CASE WHEN name LIKE 'O\\\\\'Brien' THEN 1 ELSE 0 END,           /*1*/        
        CASE WHEN name LIKE 'O\\\'Brien' ESCAPE '~' THEN 1 ELSE 0 END,  /*1*/
        CASE WHEN name = 'O\\\'Brien' THEN 1 ELSE 0 END                 /*1*/
FROM
(SELECT 'O\\\'Brien' as name) T

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89721

This is pretty standard in most SQL dialects:

SELECT person_sname
FROM people
WHERE person_sname like 'O''Brien' -- same as = 'O''Brien'

SELECT person_sname
FROM people
WHERE person_sname like 'O''Brien%' -- O'Brien plus more stuff

SELECT person_sname
FROM people
WHERE person_sname like 'O''%' -- O'plus more stuff

Note that you cannot use " for strings if ANSI_QUOTES are on, since "" are for identifiers.

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324750

like "O'%" should be sufficient. You don't need any backslashes here.

Upvotes: 0

Related Questions