What does \% and \\ mean in a SQL query?

user_9121710 0
Viewed 9387

I know the meaning of % and _ wildcard characters ,but i was stuck in a question which was using the two additional characters \% and \\,i was not able to understand what these characters actually mean in the SQL query 

SELECT productID 
FROM productList 
WHERE productName LIKE 'ab\%cd%'

and 

SELECT productID 
FROM productList 
WHERE productName LIKE 'ab\\cd%'

are these two same things or different ??

wildcard sql-like mysql
3 Answers

Since % is a special character, you have to escape it with a \ to match a literal % symbol in your data. So, 'ab\%cd%' matches the letter a, followed by the letter b, followed by a % symbol, the letter c, the letter d, then any other text (because the last % is a wildcard).

Similarly, since \ is a special character used to create escape sequences, you have to escape it to match a literal \ in a pattern, so to match a single \ you have to encode it as \\.

Bill_the_Lizard_12880

I believe the best way to see the difference is by example.

To better understand it you will need knowledge about 3 things when using LIKE operator in SQL:

  • \ is used to escape special characters to use them as normal chars
  • % is used to match any number of characters (including 0)
  • special characters are \ and % so if you want to include them literally you need to escape them, so to check for them in text column you respectively need to use \\ and \%.

Below is a table with words and true/false results for LIKE comparison with both patterns:

   word   | ab\%cd% | ab\\cd%
----------+---------+---------
 ab\      | f       | f        -- this would match second pattern but there is no "cd" at the end
 ab\cd    | f       | t        -- \\ is escaped "\", and % matches none characters
 ab\cdxzy | f       | t        -- \\ is escaped "\", and % matches character sequence "xzy"
 abcd     | f       | f        -- every string requires either "%" or "\" character after "ab"
 ab%cd    | t       | f        -- \% is escaped "%", and % matches none characters
 ab%cdxzy | t       | f        -- \% is escaped "%", and % matches character sequence "xzy"
 ab\%cd   | f       | f        -- there is no pattern which matches both chars "\%" in sequence
 ab%\cd   | f       | f        -- same as above, but characters are "%\" in sequence
user_20955340
user_6857460
Related