Tip - Wild Cards in SQLite
I suspect that I'm the only SQLite user who is surprised by the following, but I will pass the information along anyway. The only place wildcards can be used is apparently with the LIKE operator. Wildcards apparently cannot be used in character strings unless the strings are a part of a LIKE. For example, consider the following four queries.
-- #1
SELECT FT.*
FROM FactTypeTable AS FT
WHERE FT.Name IN ('Marriage', 'Divorce');
-- #2
SELECT FT.*
FROM FactTypeTable AS FT
WHERE FT.Name LIKE 'Marriage' OR FT.Name LIKE 'Divorce';
-- #3
SELECT FT.*
FROM FactTypeTable AS FT
WHERE FT.Name IN ('Marriage%', 'Divorce%');
-- #4
SELECT FT.*
FROM FactTypeTable AS FT
WHERE FT.Name LIKE 'Marriage%' OR FT.Name LIKE 'Divorce%';
In my database, these four queries produce 14 hits, 2 hits, 0 hits, and 14 hits, respectively. I have fact types named things like Marriage Bann and Divorce Record. I wanted to select only Marriage and Divorce without selecting fact types such Marriage Bann and Divorce Record, and I wanted to use query #1 to accomplish the selection. But instead, I need to use query #2. Outside of the LIKE operator, wildcards such as % are apparently just another standard character.
In the most general case, I might be selecting many more than two fact types. I will need to accomplish any such selection with a series of LIKE operators separated by OR rather than using IN followed by a list of strings.
I'm unsurprised by the wild card behaviour but astonished by that of IN() with no wild card. I've frequently used it expecting a test for an exact match. I will have to double check what is going on.