Is there a combination of LIKE and IN in SQL

Question

In SQL I  sadly  often have to use  LIKE  conditions due to databases that violate nearly every rule of normalization  I can t change that right now  But that s irrelevant to the question   Further  I often use conditions like WHERE something in  1 1 2 3 5 8 13 21  for better readability and flexibility of my SQL statements   Is there any possible way to combine these two things without writing complicated sub-selects   I want something as easy as WHERE something LIKE   bla      foo     batz    instead of this   WHERE something LIKE  bla   OR something LIKE   foo   OR something LIKE  batz     I m working with SQl Server and Oracle here but I m interested if this is possible in any RDBMS at all

User · Answer

For Sql Server you can resort to Dynamic SQL.

Most of the time in such situations you have the parameter of IN clause based on some data from database.

The example below is a little "forced", but this can match various real cases found in legacy databases.

Suppose you have table Persons where person names are stored in a single field PersonName as FirstName + ' ' + LastName. You need to select all persons from a list of first names, stored in field NameToSelect in table NamesToSelect, plus some additional criteria (like filtered on gender, birth date, etc)

You can do it as follows

-- @gender is nchar(1), @birthDate is date 

declare 
  @sql nvarchar(MAX),
  @subWhere nvarchar(MAX)
  @params nvarchar(MAX)

-- prepare the where sub-clause to cover LIKE IN (...)
-- it will actually generate where clause PersonName Like 'param1%' or PersonName Like 'param2%' or ...   
set @subWhere = STUFF(
  (
    SELECT ' OR PersonName like ''' + [NameToSelect] + '%''' 
        FROM [NamesToSelect] t FOR XML PATH('')
  ), 1, 4, '')

-- create the dynamic SQL
set @sql ='select 
      PersonName
      ,Gender
      ,BirstDate    -- and other field here         
  from [Persons]
  where 
    Gender = @gender
    AND BirthDate = @birthDate
    AND (' + @subWhere + ')'

set @params = ' @gender nchar(1),
  @birthDate Date'     

EXECUTE sp_executesql @sql, @params,    
  @gender,  
  @birthDate

User · Answer

I was also wondering for something like that  I just tested using a combination of SUBSTRING and IN and it is an effective solution for this kind of problem  Try the below query    Select   from TB YOUR T1 Where SUBSTRING T1 Something  1 3  IN   bla    foo    batz

User · Answer

In Teradata you can use LIKE ANY    ABC     PQR     XYZ     Below is an example which has produced the same results for me  --            --  CHECK ONE --            SELECT   FROM Random Table A WHERE  Lower A TRAN 1 DSC  LIKE    american express centurion bank    OR Lower A TRAN 1 DSC  LIKE    bofi federal bank    OR Lower A TRAN 1 DSC  LIKE    american express bank fsb        --            --  CHECK TWO --            SELECT   FROM Random Table  A WHERE Lower A TRAN 1 DSC  LIKE ANY     american express centurion bank      bofi federal bank      american express bank fsb

User · Answer

Starting with 2016  SQL Server includes a STRING SPLIT function  I m using SQL Server v17 4 and I got this to work for me   DECLARE  dashboard nvarchar 50  SET  dashboard    P1  P7    SELECT   from Project p JOIN STRING SPLIT  dashboard       AS sp ON p ProjectNumber LIKE sp value

User · Answer

u can even try this  Function  CREATE  FUNCTION  dbo   fn Split   text varchar 8000    delimiter varchar 20   RETURNS  Strings TABLE        position int IDENTITY PRIMARY KEY    value varchar 8000      AS BEGIN  DECLARE  index int SET  index   -1  WHILE  LEN  text   gt  0    BEGIN      SET  index   CHARINDEX  delimiter    text       IF   index   0  AND  LEN  text   gt  0         BEGIN           INSERT INTO  Strings VALUES   text            BREAK        END      IF   index  gt  1         BEGIN           INSERT INTO  Strings VALUES  LEFT  text   index - 1             SET  text   RIGHT  text   LEN  text  -  index          END      ELSE       SET  text   RIGHT  text   LEN  text  -  index       END   RETURN END   Query  select   from my table inner join  select value from fn split  ABC MOP        as split table on my table column name like     split table value

User · Answer

I have a simple solution  that works in postgresql at least  using like any followed by the list of regex  Here is an example  looking at identifying some antibiotics in a list   select   from database table where lower drug name  like any     cillin   cyclin   xacin   mycine   cephal

User · Answer

If you are using MySQL the closest you can get is full-text search   Full-Text Search  MySQL Documentation

User · Answer

If you want to make your statement easily readable  then you can use REGEXP LIKE  available from Oracle version 10 onwards    An example table   SQL gt  create table mytable  something    2  as   3  select  blabla  from dual union all   4  select  notbla  from dual union all   5  select  ofooof  from dual union all   6  select  ofofof  from dual union all   7  select  batzzz  from dual   8     Table created    The original syntax   SQL gt  select something   2    from mytable   3   where something like  bla     4      or something like   foo     5      or something like  batz     6     SOMETH ------ blabla ofooof batzzz  3 rows selected    And a simple looking query with REGEXP LIKE  SQL gt  select something   2    from mytable   3   where regexp like  something   bla foo  batz     4     SOMETH ------ blabla ofooof batzzz  3 rows selected    BUT      I would not recommend it myself due to the not-so-good performance  I d stick with the several LIKE predicates  So the examples were just for fun

User · Answer

you re stuck with the   WHERE something LIKE  bla   OR something LIKE   foo   OR something LIKE  batz     unless you populate a temp table  include the wild cards in with the data  and join like this   FROM YourTable                y     INNER JOIN YourTempTable  t On y something LIKE t something   try it out  using SQL Server syntax    declare  x table  x varchar 10   declare  y table  y varchar 10    insert  x values   abcdefg   insert  x values   abc   insert  x values   mnop    insert  y values    abc    insert  y values    b     select distinct   FROM  x x WHERE x x LIKE   abc       or x x LIKE   b     select distinct x     FROM  x             x     INNER JOIN   y  y On x x LIKE y y   OUTPUT   x ---------- abcdefg abc   2 row s  affected   x ---------- abc abcdefg   2 row s  affected

User · Answer

In Oracle RBDMS you can achieve this behavior using REGEXP LIKE function   The following code will test if the string three is present in the list expression one two three four five  in which the pipe     symbol means OR logic operation     SELECT  Success      result FROM dual WHERE REGEXP LIKE  three    one two three four five     RESULT --------------------------------- Success      1 row selected    Preceding expression is equivalent to   three one OR three two OR three three OR three four OR three five   So it will succeed   On the other hand  the following test will fail   SELECT  Success      result FROM dual WHERE REGEXP LIKE  ten    one two three four five     no rows selected   There are several functions related to regular expressions  REGEXP    available in Oracle since 10g version  If you are an Oracle developer and interested this topic this should be a good beginning Using Regular Expressions with Oracle Database

User · Answer

Sorry for dredging up an old post  but it has a lot of views  I faced a similar problem this week and came up with this pattern  declare  example table   sampletext varchar  50       insert  example values     The quick brown fox jumped over the lazy dog         Ask not what your country can do for you         Cupcakes are the new hotness       declare  filter table   searchtext varchar  50       insert  filter values    lazy        hotness        cupcakes      -- Expect to get rows 1 and 3  but no duplication from Cupcakes and Hotness select    from  example e where exists   select   from  filter f where e sampletext like       searchtext          Exists   works a little better than join  IMO  because it just tests each record in the set  but doesn t cause duplication if there are multiple matches

User · Answer

I m working with SQl Server and Oracle here but I m interested if this is possible in any RDBMS at all   Teradata supports LIKE ALL ANY syntax   ALL   every string in the list  ANY   any string in the list   -------------------------------------------------------------------          THIS expression              IS equivalent to this expression         ------------------------------ ------------------------------------      x LIKE ALL   A     B    C       x LIKE  A                                                                AND x LIKE   B                                                           AND x LIKE   C                                                                                                    x LIKE ANY   A     B    C       x LIKE  A                                                                OR x LIKE   B                                                            OR x LIKE   C                          -------------------------------------------------------------------     EDIT  jOOQ version 3 12 0 supports that syntax  Add synthetic  NOT  LIKE ANY and  NOT  LIKE ALL operators  A lot of times  SQL users would like to be able to combine LIKE and IN predicates  as in  SELECT   FROM customer WHERE last name   NOT   LIKE ANY   A     E      ESCAPE        The workaround is to manually expand the predicate to the equivalent SELECT   FROM customer WHERE last name LIKE  A   OR last name LIKE  E    jOOQ could support such a synthetic predicate out of the box    PostgreSQL LIKE ILIKE ANY  ARRAY     SELECT   FROM t WHERE c LIKE ANY  ARRAY  A      B      SELECT   FROM t WHERE c LIKE ANY     quot Do  quot    quot  at quot       db lt  gt fiddle demo  Snowflake also supports LIKE ANY LIKE ALL matching   LIKE ANY ALL Allows case-sensitive matching of strings based on comparison with one or more patterns   lt subject gt  LIKE ANY   lt pattern1 gt      lt pattern2 gt            ESCAPE  lt escape char gt      Example  SELECT    FROM like example  WHERE subject LIKE ANY    Jo oe    T e   -- WHERE subject LIKE ALL    Jo oe    J e

User · Answer

This works for comma separated values  DECLARE  ARC CHECKNUM VARCHAR MAX  SET  ARC CHECKNUM    ABC 135 MED ASFSDFSF AXX  SELECT   AND  a arc checknum LIKE        REPLACE  arc checknum          OR a arc checknum LIKE                   Evaluates to    AND  a arc checknum LIKE   ABC   OR a arc checknum LIKE   135   OR a arc checknum LIKE   MED   OR a arc checknum LIKE   ASFSDFSF   OR a arc checknum LIKE   AXX      If you want it to use indexes  you must omit the first     character

User · Answer

do this  WHERE something       in   bla    foo    batz   OR       something       in   tra    la    la     or  WHERE something       in  select col from table where

User · Answer

I know this is very late  but I had a similar situation   I needed a  Like In  operator for a set of stored procedures I have  which accept many parameters and then uses those parameters to aggregate data from multiple RDBMS systems  thus no RDBMS-specific tricks would work  however the stored procedure and any functions will run on MS SQL Server  so we can use T-SQL for the functionality of generating the full SQL statements for each RDBMS  but the output needs to be fairly RDBMS-independent   This is what I ve come up with for the moment to turn a delimited string  such as a parameter coming into a stored procedure  into a block of SQL   I call it  Lichen  for  LIKE IN    Get it   Lichen sql  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO --                                                                         -- Lichen - Scalar Valued Function -- Returns nvarchar 512  of  LIKE IN  results   See further documentation  -- CREATOR  Norman David Cooke -- CREATED  2020-02-05 -- UPDATED  --                                                                         CREATE OR ALTER FUNCTION Lichen        -- Add the parameters for the function here      leadingAnd bit   1       delimiter nchar 1              colIdentifier nvarchar 64        argString nvarchar 256    RETURNS nvarchar 512  AS BEGIN     -- Declare the return variable here     DECLARE  result nvarchar 512       -- set delimiter to detect  add more here to detect a delimiter if one isn t provided      DECLARE  delimit nchar 1            IF NOT  delimiter    delimit          SET  delimit    delimiter       -- check to see if we have any delimiters in the input pattern     IF CHARINDEX  delimit   argString   gt  1  -- check for the like in delimiter     BEGIN  -- begin  like in  branch having found a delimiter         -- set up a table variable and string split the provided pattern into it          DECLARE  lichenTable TABLE   id   int  IDENTITY 1 1  NOT NULL  line NVARCHAR 32           INSERT INTO  lichenTable SELECT   FROM STRING SPLIT  argString                -- setup loop iterators and determine how many rows were inserted into lichen table         DECLARE  loopCount int   1         DECLARE  lineCount int          SELECT  lineCount   COUNT    from  lichenTable          -- select the temp table  to see whats inside for debug          --select   from  lichenTable          -- BEGIN AND wrapper block for  LIKE IN  if bit is set         IF  leadingAnd   1             SET  result     AND            ELSE             SET  result                 -- loop through temp table to build multiple  LIKE  x  OR  blocks inside the outer AND wrapper block         WHILE    loopCount IS NOT NULL  AND   loopCount  lt    lineCount           BEGIN -- begin loop through  lichenTable             IF   loopcount   1  -- the first loop does not get the OR in front                 SELECT  result   CONCAT  result        colIdentifier    LIKE      line        FROM  lichenTable WHERE id    loopCount             ELSE  -- but all subsequent loops do                 SELECT  result   CONCAT  result    OR     colIdentifier    LIKE      line        FROM  lichenTable WHERE id    loopCount             SET  loopcount    loopCount   1     -- increment loop         END -- end loop through  lichenTable          -- set final parens after lichenTable loop         SET  result   CONCAT  result            END  -- end  like in  branch having found a delimiter     ELSE -- no delimiter was provided     BEGIN   -- begin  no delimiter found  branch         IF  leadingAnd   1              SET  result   CONCAT   AND     colIdentifier    LIKE        argString                 ELSE             SET  result   CONCAT       colIdentifier    LIKE        argString             END     -- end  no delimiter found  branch      -- Return the result of the function     RETURN  result END  -- end lichen function  GO   The delimiter detection is possibly planned  but for now it defaults to a semicolon so you can just put default in there   There are probably bugs in this  The  leadingAnd parameter is just a bit value to determine if you want a leading  AND  put in front of the block so it fits in nicely with other WHERE clause additions   Example Usage  with delimiter in argString   SELECT  dbo   Lichen       default        --  leadingAND  bit  default  1    default        --  delimiter  nchar 1   default          foo bar       --  colIdentifier  nvarchar 64   this is the column identifier     01  02   03   --  argString  nvarchar 256   this is the input string to parse  LIKE IN  from   GO   Will return a nvarchar 512  containing    AND   foo bar LIKE  01   OR foo bar LIKE  02   OR foo bar LIKE   03        It will also skip the block if the input does not contain a delimiter   Example Usage  without delimiter in argString   SELECT  dbo   Lichen       default        --  leadingAND  bit  default  1    default        --  delimiter  nchar 1   default          foo bar       --  colIdentifier  nvarchar 64   this is the column identifier     01            --  argString  nvarchar 256   this is the input string to parse  LIKE IN  from   GO   Will return a nvarchar 512  containing    AND foo bar LIKE  01     I m going to continue work on this  so if I ve overlooked something  glaringly obvious or otherwise  please feel free to comment or reach out

User · Answer

In Oracle you can use a collection in the following way   WHERE EXISTS  SELECT 1                 FROM TABLE ku  vcnt  bla      foo     batz                    WHERE something LIKE column value    Here I have used a predefined collection type ku  vcnt  but you can declare your own one like this   CREATE TYPE my collection AS TABLE OF VARCHAR2 4000

User · Answer

May be you think the combination like this   SELECT     FROM    table t INNER JOIN     SELECT   FROM  VALUES  bla     foo     batz    AS list col    l ON t column  LIKE     l Col       If you have defined full text index for your target table then you may use this alternative   SELECT     FROM    table t WHERE CONTAINS t column    bla   OR  foo   OR  batz

User · Answer

I would suggest using a TableValue user function if you d like to encapsulate the Inner Join or temp table techniques shown above   This would allow it to read a bit more clearly   After using the split function defined at  http   www logiclabz com sql-server split-function-in-sql-server-to-break-comma-separated-strings-into-table aspx  we can write the following based on a table I created called  Fish   int id  varchar 50  Name   SELECT Fish   from Fish      JOIN dbo Split   ass  e        as Splits      on Name like Splits items    items is the name of the output column from the split function    Outputs   1   Bass 2   Pike 7   Angler 8   Walleye

User · Answer

One approach would be to store the conditions in a temp table  or table variable in SQL Server  and join to that like this   SELECT t SomeField FROM YourTable t    JOIN  TempTableWithConditions c ON t something LIKE c ConditionValue

User · Answer

No answer like this   SELECT   FROM table WHERE something LIKE   bla   foo  batz      In oracle no problem

User · Answer

Use an inner join instead   SELECT     FROM SomeTable JOIN  SELECT  bla   AS Pattern  UNION ALL SELECT   foo   UNION ALL SELECT  batz   UNION ALL SELECT  abc    AS Patterns ON SomeTable SomeColumn LIKE Patterns Pattern

User · Answer

I may have a solution for this  although it will only work in SQL Server 2008 as far as I know  I discovered that you can use the row-constructor described in https   stackoverflow com a 7285095 894974 to join a  fictional  table using a like clause  It sounds more complex then it is  look   SELECT  name      userID      name      town      email  FROM usr join  values   hotmail     gmail     live    as myTable myColumn  on email like     myTable myColumn        This will result in all users with an e-mail adres like the ones provided in the list  Hope it s of use to anyone  The problem had been bothering me a while

User · Answer

With PostgreSQL there is the ANY or ALL form   WHERE col LIKE ANY  subselect     or  WHERE col LIKE ALL  subselect     where the subselect returns exactly one column of data

User · Answer

There is no combination of LIKE  amp  IN in SQL  much less in TSQL  SQL Server  or PLSQL  Oracle    Part of the reason for that is because Full Text Search  FTS  is the recommended alternative   Both Oracle and SQL Server FTS implementations support the CONTAINS keyword  but the syntax is still slightly different   Oracle   WHERE CONTAINS t something   bla OR foo OR batz   1   gt  0   SQL Server   WHERE CONTAINS t something    bla   OR  foo   OR  batz       The column you are querying must be full-text indexed   Reference    Building Full-Text Search Applications with Oracle Text  Understanding SQL Server Full-Text

User · Answer

Another solution  should work on any RDBMS  WHERE EXISTS  SELECT 1                 FROM  SELECT  bla   pattern FROM dual UNION ALL                       SELECT   foo          FROM dual UNION ALL                       SELECT  batz          FROM dual                 WHERE something LIKE pattern   The inner select can be replaced by another source of patterns like a table  or a view  in this way  WHERE EXISTS  SELECT 1                 FROM table of patterns t                WHERE something LIKE t pattern   table of patterns should contain at least a column pattern  and can be populated like this  INSERT INTO table of patterns pattern  VALUES   bla     INSERT INTO table of patterns pattern  VALUES    foo     INSERT INTO table of patterns pattern  VALUES   batz

[sql] Is there a combination of "LIKE" and "IN" in SQL?

Examples related to sql

Examples related to sql-server

Examples related to oracle

Examples related to tsql

Examples related to plsql