Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does \ (backslash) mean in an SQL query?

Tags:

sql

sql-like

I have the following query

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a\%'

will that result in answers that have any char+a+\+whatever?

is something like Pa\pe valid as a result?

are Ca% or _a% valid answers maybe?

how does \ behave normally inside an SQL query??

like image 974
Polux Avatar asked Mar 05 '13 01:03

Polux


2 Answers

% is a wildcard character that matches zero or more characters in a LIKE clause. _ is a wildcard character that maches exactly one character in a LIKE clause.

\ is a special character known as an escape character that indicates that the character directly following it should be interpreted literally (useful for single quotes, wildcard characters, etc.).

For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a%'

will select records with txt1 values of 'xa1', 'xa taco', 'ya anything really', etc.

Now let's say you want to actually search for the percent sign. In order to do this you need a special character that indicates % should not be treated as a wildcard. For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a\%'

will select records with txt1 values of 'ba%' (but nothing else).

Finally, a LIKE clause would typically contain a wildcard (otherwise you could just use = instead of LIKE). So you might see a query containing \%%. Here the first percent sign would be treated as a literal percent sign, but the second would be interpreted as a wildcard. For example:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a\%%'

will select records with txt1 values of 'da%something else', 'fa% taco', 'ma% bunch of tacos', etc.

like image 62
chrislopresto Avatar answered Nov 03 '22 18:11

chrislopresto


The LIKE clause allows you to find text when you don't know the exact value, such as names beginning with JO would be

LIKE 'JO%'

However, if you are search for something ending with a%, then you need to tell SQL to treat the % as part of what you are searching for. In your example, you are looking for a 3 character string, you don't care what the first letter is, but has to end with a%.

like image 34
Sparky Avatar answered Nov 03 '22 20:11

Sparky