[SQLite] How to use LIKE with IN

[Zvoni]

Hi Folks,
everyone knows the Problem trying to compare a column against multiple values with wildcards.
This usually results in "constructs" like

SELECT DISTINCT SomeTextField
FROM Table
WHERE
SomeTextField LIKE 'Value1%' OR
SomeTextField LIKE '%Value2' OR
SomeTextField LIKE '%Value3%'

because a "SELECT ...... FROM ..... WHERE SomeTextField LIKE IN ('Value1%', '%Value2', '%Value3%')" doesn't work

I was researching something related to it, when i stumbled across the information, that you can use the LIKE-Operator against a column (right-hand-side)
along the lines of

SELECT DISTINCT
t1.SomeTextField FROM tbl_test As T1
INNER JOIN tbl_test As t2
ON t1.SomeTextField LIKE t2.SomeOtherTextField

Note: You can use it against a column of the same table or a column of a different table

Inspired by this, i got the following idea:

WITH cte(X) AS (
    SELECT 'Text%' UNION
    SELECT 'Hello%' UNION
    SELECT '%World%')
SELECT DISTINCT
t.SomeTextField
FROM tbl_test As t
INNER JOIN cte As c
ON t.SomeTextField LIKE c.x

.... and it works.

I'm aware, that you cannot use parameters that way, OTOH in that case you could use a (temp.) table instead of the CTE.

The "variable" Part of the Query is obviously the SELECT inside the CTE
For people "constructing" their queries from the Frontend, they could could go about like

MyArray(0)="SELECT 'Text%'"  //No trailing space!
MyArray(1)="SELECT 'Hello%'" //No trailing space!
MyArray(2)="SELECT '%World%'" //No trailing space!

and then Join the Array to a String with " UNION " as Delimiter (Note the leading and trailing space around UNION).

'Using CTE
MyArray(0)="SELECT 'Text%'"  //No trailing space!
MyArray(1)="SELECT 'Hello%'" //No trailing space!
MyArray(2)="SELECT '%World%'" //No trailing space!

SQLString = "WITH cte(X) AS (" 
SQLString = SQLString & Join(MyArray, " UNION ")
SQLString = SQLString & ") "
SQLString = SQLString & "SELECT DISTINCT t.SomeTextField "
SQLString = SQLString & "FROM tbl_test As t "
SQLString = SQLString & "INNER JOIN cte As c "
SQLString = SQLString & "ON t.SomeTextField LIKE c.x"
'Execute Query

'Using tmp-table
SQLString = "INSERT INTO tbl_tmp (tmpTextColumn) VALUES ('Text%'),('Hello%'),('%World%')"  'Note the Wildcards! Here you would be able to use Parameters!!
'Execute INSERT
SQLString = "SELECT DISTINCT t.SomeTextField "
SQLString = SQLString & "FROM tbl_test As t "
SQLString = SQLString & "INNER JOIN tbl_temp As c "
SQLString = SQLString & "ON t.SomeTextField LIKE c.tmpTextColumn"
'Execute Query

Note: the DISTINCT is important, e.g. for "Hello World" the above would return two results without DISTINCT

link to fiddle: https://dbfiddle.uk/?rdbms=sqlite_3....abe40290f16578

[wqweto]

Btw, you don't need DISTINCT if you use WHERE EXISTS(SELECT 1/0 FROM cte As c WHERE t.SomeTextField LIKE c.x) correlated query instead of joining the cte.

These correlated queries tend to produce faster execution plans compared to filtering by JOINs because with correlated subqueries the columns of the "joined" tables (i.e. cte in OP) are not available in the SELECT clause and cannot be returned so a somewhat more optimal plan can be produced.

Another reason for more optimal plans is that with correlated queries the number of rows in the resultset cannot exceed the number of rows in original tbl_tmp while with JOINs the number of rows can explode if a row from tbl_tmp matches multiple filtering rows from cte (so you need a clutch like DISTINCT/GROUP BY which is slow and IMO a sure sign the query is suboptimal).

cheers,
</wqw>

[Zvoni]

hmm...
I'll take your word for it, since i've never used EXISTS in that context (don't laugh, it's true),
and IIRC, not all DBMS provide the EXISTS-Operator/Function. So i'm more used to use the IN-operator or an INNER JOIN
My Sample should work with any DBMS which supports CTE

[wqweto]

My Sample should work with any DBMS which supports CTE

Yes, it will work, provided that there is no duplicate 'Hello World' in SomeTextField -- then the DISTINCT will clobber the second row.

If there aren't supposed to be any duplicates in SomeTextField you might want to put a UNIQUE/PRIMARY KEY in the DDL just to be explicit.

The correlated query does not care if there are duplicates or not, if there is UNIQUE or not -- it works in any case as it does not use DISTINCT on final result.

Btw, EXISTS predates IN *and* JOINs which were introduced in ANSI SQL-92 (if memory serves me well). The old syntax was

SELECT DISTINCT t.SomeTextField
FROM tbl_test As t, cte As c
WHERE t.SomeTextField LIKE c.x

. . . with no JOIN clause available at all (this still works in sqlite).

cheers,
</wqw>