Archive for March, 2012

SQLite escape character

March 17th, 2012 2 comments

SQLite3 is small DB engine. This is not a client and server type, but it could be embedded DB engine. It is very handy for personal use.

If we query simple English word, we do not need to care about escape characters. But if we need to look up search word with special characters, we need to know how to escape special characters which has special meaning in SQL sentence.

[ LIKE operator ]
SQLite3 LIKE operator is same to standard SQL. We can use wildcards ‘%’ (matches with multiple characters) and ‘_’ (matches with single character). If we need to search word with those wildcards, we need to use ESCAPE clause.

SELECT * FROM hoge WHERE key LIKE '% test^_case %' ESCAPE '^';

Character which is used in ESCAPE clause is also needed to escape.

Consideration of single quote and double quote “:
– We can use double quote character within single quoted text.
– We can use single quote character within double quoted text.
– We need to escape quote character by quote character itself when we need to mix both single and double quote in a text.

SELECT * FROM hoge WHERE key LIKE 'I''m reading the "SNOW WHITE"';

[ GLOB operator ]
GLOB is unique operator of SQLite3. GLOB allows to use small subset of regular expression. LIKE is case insensitive operator, but GLOB is case sensitive operator.

Let me talk about only how to escape special characters here. We need to escape [ ] * ? for GLOB operator. If we need to match those characters, we use [] to wrap them.

SELECT * FROM hoge WHERE key GLOB 'How are you[?] *';

You can wrap ‘[‘ or ‘]’ also. For example, if we need to match text ‘[Y/n]’, query would be like follows.

SELECT * FROM hoge WHERE key GLOB '[[]Y/n[]]';

Consideration of quote characters is same to LIKE operator.

Categories: Programming Tags: