How to escape a % sign in sqlite?

Question

I do a full text search using LIKE clause and the text can contain a '%'.

What is a good way to search for a % sign in an sqlite database?

I did try

SELECT * FROM table WHERE text_string LIKE '%[%]%' 

but that doesn't work in sqlite.

Answer 1

From the SQLite documentation:

If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character. This character may be used in the LIKE pattern to include literal percent or underscore characters. The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively.

Breaking it down:

• If the optional ESCAPE clause is present, then the expression following the ESCAPE keyword must evaluate to a string consisting of a single character.

  You add the ESCAPE clause at the end of the WHERE clause, followed by a single character string.

• This character may be used in the LIKE pattern to include literal percent or underscore characters.

  You use this escape character in the value of the LIKE operator to escape %s or _s.

• The escape character followed by a percent symbol (%), underscore (_), or a second instance of the escape character itself matches a literal percent symbol, underscore, or a single escape character, respectively.

  Possible escaping examples:

  • WHERE text_string LIKE "escaping \% character" ESCAPE "\"
  • WHERE text_string LIKE "escaping \_ character" ESCAPE "\"
  • WHERE text_string LIKE "escaping \\ character" ESCAPE "\"