Using SQL LIKE and IN together

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开发者_Go百科 am looking for?

How about using a substring with IN.

select * from tablename where substring(column,1,4) IN ('M510','M615','M515','M612')

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.

You'll need to use multiple LIKE terms, joined by OR.

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%';

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%'
    )

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')

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.

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%')

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) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 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+'%';

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 & 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

select * 
from tablename
where regexp_like (column, '^M510|M615|^M515|^M612')

Note: This works even if say, we want the code M615 to match if it occurs in the middle of the column. The rest of the codes will match only if the column starts with it.