SQLite CASE LIKE returning NULL

user_14106021 0
Viewed 104

I'm trying to run a query to check for a substring, and when that substring exists I want the query to return a value of my choosing.

I run SELECT CASE WHEN COLUMNNAME LIKE '%"cashtag":null%' THEN 'NO CASH TAG' END COLUMNNAME FROM TABLENAME

I've run this same command with different variables and it worked fine. But with it written this way to look for "cashtag":null I get return values of NULL in my query result.

I've provided an image of what one of my cells in COLUMNAME looks like: enter image description here

SO for instance, for this particular cell, I expect to get a return of NO CASH TAG in my query result. But it returns NULL. Not sure what it is about this particular "cashtag":null value that is throwing things off. As I stated, I have used other values with a similar format with the CASE WHEN LIKE functions and they worked just fine.

case sql-like sqlite
1 Answers

The only case I can think of that this may happen is if COLUMNAME contains NUL character (ASCII code 0) before the substring '"cashtag":null'.
If this happens, SQLite's LIKE operator can't locate the substring after the NUL character.
For example:

SELECT 'abc' || char(0) || 'def' LIKE '%e%'

returns 0 (FALSE).
But:

SELECT 'abc' || char(0) || 'def' LIKE '%a%'

returns 1 (TRUE).

You can check for NUL characters with:

SELECT INSTR(COLUMNNAME, char(0)) [position of NUL char]
FROM TABLENAME

In any case, use INSTR() instead of LIKE which will work even with NUL characters:

SELECT 
  CASE WHEN INSTR(COLUMNNAME, '"cashtag":null') THEN 'NO CASH TAG' END COLUMNNAME 
FROM TABLENAME

The only difference here is that LIKE is by default case insensitive but INSTR() case sensitive.
So INSTR(COLUMNNAME, '"cashtag":null') will not find '"Cashtag":NULL'.
If you need case insensitive search use UPPER() function also:

SELECT 
  CASE WHEN INSTR(UPPER(COLUMNNAME), UPPER('"cashtag":null')) THEN 'NO CASH TAG' END COLUMNNAME 
FROM TABLENAME

Now the problem is that UPPER() works only for the ASCII characters of the string and not UNICODE characters beyond the ASCII range.

user_104988280
Related