Using SQL LIKE and IN together

Question

Is there a way to use LIKE and IN together   I want to achieve something like this   SELECT   FROM tablename WHERE column IN   M510     M615     M515     M612       So basically I want to be able to match the column with a bunch of different strings   Is there another way to do this with one query or will I have to loop over the array of strings I am looking for

User · Answer

You ll need to use multiple LIKE terms  joined by OR

User · Answer

SELECT   FROM tablename   WHERE column IN       select column from  tablename      where column like  M510        or column like  M615        OR column like  M515        or column like M612

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  M510    M615    M515    M612        as split table on my table column name like     split table value

User · Answer

You can use a sub-query with wildcards    SELECT  Valid Expression   WHERE  Source Column  LIKE  SELECT   Column  --FROM TABLE    Or you can use a single string    SELECT  Valid Expression   WHERE  Source Column  LIKE    Source       Column

User · Answer

substr  column name           desired starting position  numeric              characters to include  numeric    in   complete as usual     Example  substr  column name  1 4  in   M510   M615    M515    M612

User · Answer

How about using a substring with IN   select   from tablename where substring column 1 4  IN   M510   M615   M515   M612

User · Answer

Use the longer version of IN which is a bunch of OR   SELECT   FROM tablename  WHERE column LIKE  M510   OR column LIKE  M615   OR column LIKE  M515   OR column LIKE  M612

User · Answer

You can do it by in one query by stringing together the individual LIKEs with ORs   SELECT   FROM tablename WHERE column LIKE  M510   OR    column LIKE  M615   OR    column LIKE  M515   OR    column LIKE  M612      Just be aware that things like LIKE and per-row functions don t always scale that well  If your table is likely to grow large  you may want to consider adding another column to your table to store the first four characters of the field independently   This duplicates data but you can guarantee it stays consistent by using insert and update triggers  Then put an index on that new column and your queries become   SELECT   FROM tablename WHERE newcolumn IN   M510   M615   M515   M612      This moves the cost-of-calculation to the point where it s necessary  when the data changes   not every single time you read it  In fact  you could go even further and have your new column as a boolean indicating that it was one of the four special types  if that group of specials will change infrequently   Then the query would be an even faster   SELECT   FROM tablename WHERE is special   1    This tradeoff of storage requirement for speed is a useful trick for larger databases - generally  disk space is cheap  CPU grunt is precious  and data is read far more often than written  By moving the cost-of-calculation to the write stage  you amortise the cost across all the reads

User · Answer

I tried another way  Say the table has values      1   M510     2   M615     3   M515     4   M612     5   M510MM     6   M615NN     7   M515OO     8   M612PP     9   A     10  B     11  C     12  D   Here cols 1 to 8 are valid while the rest of them are invalid     SELECT COL VAL     FROM SO LIKE TABLE SLT    WHERE  SELECT DECODE SUM CASE                               WHEN INSTR SLT COL VAL  COLUMN VALUE    0 THEN                                1                               ELSE                                0                             END                           0                           FALSE                            TRUE               FROM TABLE SYS DBMS DEBUG VC2COLl  M510    M615    M515    M612                 TRUE    What I have done is using the INSTR function  I have tried to find is the value in table matches with any of the values as input  In case it does  it will return it s index  i e  greater than ZERO  In case the table s value does not match with any of the input  then it will return ZERO  This index I have added up  to indicate successful match   It seems to be working   Hope it helps

User · Answer

For a perfectly dynamic solution  this is achievable by combining a cursor and a temp table  With this solution you do not need to know the starting position nor the length  and it is expandable without having to add any OR s to your SQL query   For this example  let s say you want to select the ID  Details  amp  creation date from a table where a certain list of text is inside  Details    First create a table FilterTable with the search strings in a column called Search   As the question starter requested   insert into  DATABASE  dbo FilterTable select  M510  union select  M615  union select  M515  union  select  M612    Then you can filter your data as following   DECLARE  DATA NVARCHAR MAX   CREATE TABLE  Result  ID uniqueIdentifier  Details nvarchar MAX   Created datetime   DECLARE DataCursor CURSOR local forward only FOR   SELECT       Search       FROM  DATABASE  dbo FilterTable  OPEN DataCursor    FETCH NEXT FROM DataCursor INTO  DATA     WHILE   FETCH STATUS   0    BEGIN         insert into  Result         select ID  Details  Created         from  DATABASE  dbo Table  nolock          where Details like  DATA     FETCH NEXT FROM DataCursor INTO  DATA    END     CLOSE DataCursor    DEALLOCATE DataCursor   select   from  Result drop table  Result   Hope this helped

[mysql] Using SQL LIKE and IN together

Examples related to mysql