CODEWITHSUNDEEP
mysqlwildcardsql-like
LocalHost
LocalHost

Reputation: 910

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

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 ??

Upvotes: 4

Views: 12387

Answers (3)

Bill the Lizard
Bill the Lizard

Reputation: 405765

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 \\.

Upvotes: 6

Brian
Brian

Reputation: 7289

The \% and \_ sequences are used to search for literal instances of % and _ in pattern-matching contexts where they would otherwise be interpreted as wildcard characters.

For \\ it searches for a single back slash \.

Ref: MySQL 8.0 Reference Manual, 9.1.1 String Literals, Table 9.1 Special Character Escape Sequences

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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

Upvotes: 1

Related Questions