Menu
I can't figure out what the underscore character does in an SQLite like statement. The wildcard character, %, is probably the same as in most other SQL databases.
So, what does the _ character do?
631
Introduction to SQLite LIKE operator Note that you can also use the LIKE operator in the WHERE clause of other statements such as the DELETE and UPDATE . The percent sign % wildcard matches any sequence of zero or more characters.
To be case insensitive on firstname , write this: select * from tbl where firstname='john' COLLATE NOCASE and lastname='doe' . It's specific to that one column, not the entire where clause.
The EXCEPT operator in SQLite is equivalent to the MINUS operator in Oracle.
It is standard SQL that in LIKE expressions:
% matches any sequence of characters, including an empty one. It is equivalent to .* in a regular expression._ matches a single character. It is equivalent to . in a regular expression.You can choose a character for escaping %, _ and itself itself with:
... WHERE expr LIKE 'a_b%c\\d\%\_' ESCAPE '\' This will match a×b×××c\d%_ or a×bc\d%_ but not abc\d%_ nor a×b×××cd%_.
Additionnally with SQLite you have the GLOB keyword which behaves exactly the same way, except that % becomes * and _ becomes ?.
121
The underscore is also the same as in most other SQL databases and matches any single character (i.e. it is the same as . in a regular expression). From the fine manual:
An underscore ("_") in the LIKE pattern matches any single character in the string.
For example:
-- The '_' matches the single 'c' sqlite> select 'pancakes' like 'pan_akes'; 1 -- This would need '__' to match the 'ca', only one '_' fails. sqlite> select 'pancakes' like 'pan_kes'; 0 -- '___' also fails, one too many '_'. sqlite> select 'pancakes' like 'pan___kes'; 0 And just to make sure the results make sense: SQLite uses zero and one for booleans.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
