[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