SQL like for comma separated input

Question

I want to write a stored procedure in which I want to run a query for multiple input which comes as a comma separated string. Just like we have in for exact match, can I have something like in too?

Input:

51094,51096,512584

Attempting to do:

select * from table where column like ('%51094%','%51096%','%512584%')

My query should iterate through each input and get the column which matches the pattern.

I have already tried following:

Contains(Column, '"*51094*" or "*51096*" or "*512584*")

But can't configure freetext search now.

Source: Is there a combination of "LIKE" and "IN" in SQL?

All the proposed types in: How to use SQL LIKE condition with multiple values in PostgreSQL?

None seems to be working.

Please suggest a simple way.

Is this MySQL or MS SQL Server? You currently have both tags. — Mureinik, Apr 01 '14 at 06:12
have a look at this thread, might help you: http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause — Fizor, Apr 01 '14 at 08:06

score 0 · Answer 1 · answered Apr 01 '14 at 06:17

0

Try with first explode your input

$arr = explode($Input,",");
column like "%".$arr[0]."%" OR
column like "%".$arr[1]."%" OR
column like "%".$arr[2]."%"

answered Apr 01 '14 at 06:17

Sadikhasan

18,365
21
80
122

The length is not fixed. It can be as long as 600 to 700 entries. I can't write your solution for such a case. – Ross Cooper Apr 01 '14 at 06:20

score 0 · Answer 2 · answered Apr 01 '14 at 09:56

This function you can use, no any mandatory to give comma only you can give special character.

ALTER function [dbo].[SplitString] (@String nvarchar(4000), @Delimiter char(1)) Returns @Results Table (Items nvarchar(50)) As Begin Declare @Index int Declare @name nvarchar(20) Declare @Slice nvarchar(50)

Select @Index = 1 If @String Is NULL Return

While @Index != 0 Begin Select @Index = CharIndex(@Delimiter, @String) If @Index <> 0

Select @Slice = left(@String, @Index - 1)

else

Select @Slice = @String Insert into @Results(Items) Values (@Slice) Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break

End Return End

score 0 · Answer 3 · answered Apr 01 '14 at 10:26

Looped the items and got it done.

Select * into #temp_inputIds from dbo.Split(@InputIds,',')

    DECLARE @ID varchar (50)
    DECLARE IDs CURSOR LOCAL FOR select items from #temp_inputIds

    OPEN IDs
    FETCH NEXT FROM IDs into @ID
    WHILE @@FETCH_STATUS = 0
    BEGIN



    Select @SQL = 'Select component_id,'+@ID+' as pub_id from component_presentations where CONTENT like ''%' + @ID + '%'''



    FETCH NEXT FROM IDs into @ID
    END

    CLOSE IDs
    DEALLOCATE IDs

SQL like for comma separated input

3 Answers3