CODEWITHSUNDEEP
sqlsql-server
Win
Win

Reputation: 2613

Select * from table where column = (any value from a comma delimited string)

The user selects various words from a drop down list and these values get added into a comma delimited string. When passing the string to a stored procedure I want it to select * from a table where that word exists.

Table

id----word

1-----cat

2-----dog

3-----mouse

4-----dog

string that is passed into the stored procedure is cat, dog so returning columns 1, 2 and 4.

Is there a way of doing this in sql server?

Upvotes: 0

Views: 6687

Answers (5)

arigato gozaimasu
arigato gozaimasu

Reputation: 63

CREATE FUNCTION 
ALTER  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

----

select * from yourtable where column in ( select value from fn_Split(@para1,',')

Upvotes: 0

armen
armen

Reputation: 1283

you first need to make a function SplitCSV :

CREATE FUNCTION [dbo].[SplitCSV] (@CSVString VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
    DECLARE @pos INT;
    DECLARE @slice VARCHAR(8000);

    SELECT @pos = 1;
    IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;

    WHILE @pos!= 0
    BEGIN
        SET @pos = CHARINDEX(@Delimiter,@CSVString);
        IF @pos != 0
            SET @slice = LEFT(@CSVString, @pos - 1);
        ELSE
            SET @slice = @CSVString;

        IF( LEN(@slice) > 0)
            INSERT INTO @temptable(Items) VALUES (@slice);

        SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos);
        IF LEN(@CSVString) = 0 BREAK;
    END
    RETURN
END
GO

then you can use it like :

SELECT *
FROM myTable
WHERE ID IN (
    SELECT items FROM [dbo].[SplitCSV]('1,2,3,4,5', ',')
)

Upvotes: 2

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

SELECT *
FROM Table
WHERE '%,' + Word + ',%' LIKE ',' + @your_csv_param + ','

Extra commas at the begin and end of parameter and column are to prevent search to match cat with catfish for example.

Upvotes: 1

Tan Suiwseng
Tan Suiwseng

Reputation: 77

If you want select all animal except mouse , you can use NOT IN

SELECT * FROM
TABLE
WHERE Word Not IN('Mouse')

So you can avoid type many type of animal

Upvotes: 0

mishik
mishik

Reputation: 10003

Use IN:

SELECT *
FROM   your_table
WHERE  word IN ('cat', 'dog')

Upvotes: 4

Related Questions