In the context of this code challenge, can we match for a specific casing of text?
Answer
Yes, this is possible.
By default, text matching using LIKE is case-insensitive, however we can utilize a special type of statement in SQLite, known as a PRAGMA statement. PRAGMA statements are a specific type of statement in SQLite, and can be used to modify certain behaviors of the available functionality.
The following is an example of how it can be applied.
/* Run this statement to allow case-sensitive
matching on the LIKE operator */
PRAGMA case_sensitive_like = true;
/* This statement will now only match text that
contains the exact casing of "Code" */
SELECT *
FROM table
WHERE column LIKE '%Code%';
If i made another select statement, does the pragma statement still apply to it as well? (If it does, how can you make the pragma statement apply to only one/two/… select statements and then not apply to the rest?)
I had to read this a few times to realize PRAGMA would not be part of the statement to search for columns with ‘code’ in the title… If you’re using this PRAGMA statement, will it apply to all further code? Or just the following statement? That leads me to wonder how to turn it off if it applies indefinitely.
Alright, so I was curious as well and found a way.
Let’s say we write:
PRAGMA case_sensitive_like = true;
SELECT *
FROM news
WHERE title LIKE '%bitcoin%'
AND publisher = 'MarketWatch \(blog\)';
PRAGMA case_sensitive_like = false;
SELECT *
FROM news
WHERE title LIKE '%BITcoin%'
AND publisher = 'MarketWatch \(blog\)';
The second query will find the same result, even though I spelled it ‘BITcoin’.
So, we can just add another PRAGMA statement where we equal it to false, which will turn it off.
Kind of repetitive though, so if anyone knows a better way, let’s hear it.
Yes, the PRAGMA case_sensitive_like = true; statement in SQLite affects the behavior of the LIKE operator for all subsequent queries in the same session or connection. Once set, it remains in effect until it is changed again, or until the session or connection is closed.
To apply the PRAGMA statement to only specific SELECT statements and then revert back for subsequent queries, you would need to reset the PRAGMA setting after your specific queries. Here’s how you can do it:
Set PRAGMA case_sensitive_like = true; for case-sensitive matching.
Run your SELECT statements where you need case-sensitive matching.
After those specific SELECT statements, set PRAGMA case_sensitive_like = false; to revert to the default case-insensitive matching for subsequent queries.
Here’s an example of how you might structure this:
-- Enable case-sensitive matching
PRAGMA case_sensitive_like = true;
-- Queries that need case-sensitive matching
SELECT * FROM table WHERE column LIKE '%Code%';
-- Reset to default case-insensitive matching
PRAGMA case_sensitive_like = false;
-- Queries that follow will use default case-insensitive matching
SELECT * FROM table WHERE column LIKE '%code%';
This approach allows you to control the scope of the PRAGMA setting and apply it only to the queries where it’s needed. Remember, PRAGMA settings are connection-specific, so if you have multiple connections to the database, you’ll need to set the PRAGMA for each connection where you want it to apply.