Results 1 to 4 of 4

Thread: [SQLite] How to use LIKE with IN

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,523

    [SQLite] How to use LIKE with IN

    Hi Folks,
    everyone knows the Problem trying to compare a column against multiple values with wildcards.
    This usually results in "constructs" like
    Code:
    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
    Code:
    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:
    Code:
    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
    Code:
    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).

    Code:
    '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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,230

    Re: [SQLite] How to use LIKE with IN

    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>

  3. #3

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,523

    Re: [SQLite] How to use LIKE with IN

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,230

    Re: [SQLite] How to use LIKE with IN

    Quote Originally Posted by Zvoni View Post
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width