SQL QUERY LIKE & IN TOGETHER

Question

I have a column in table which has following values. Names are separed by comma..

   ProjID    Names
   1         Adam , Babita Tripathy, Alex, Mihir , Farhad
   2         SaravanaKumar, Shruthi, Arthi, Suneeth

I am passing input value to stored procedure to fetch values. The input value is multiple names. If input is (Arthi,SaravanaKumar) i need to get both the rows as result because ProjID 1 and 2 has one of the input names. How can i achive it. Please help..

Search Condition.

    IF @Names<>''  
      SET @condition = @condition+' ProdType.NamesLIKE'''+'%'+RTRIM(@Names)+'%'' AND'

have a look on http://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values — Kundan Singh Chouhan, Sep 29 '13 at 05:10
Tanknks, I am trying to create a search condition dynamically..I am able to create it. Please see the search condition in my updated question.. — sk7730, Sep 29 '13 at 05:17
I need to split input names by comma and then need to create a dynamic search condition.. — sk7730, Sep 29 '13 at 05:19
You should (1) normalize database or (at least) (2) use XML to store that array of names. — Bogdan Sahlean, Sep 29 '13 at 06:02

score 1 · Answer 1 · answered Sep 29 '13 at 09:08

You can use a function to split he input string

IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID('UF_CSVToTable'))
 DROP FUNCTION UF_CSVToTable
GO

CREATE FUNCTION UF_CSVToTable
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(MAX))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)
 DECLARE @tTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp(sID) VALUES (@sTemp)                   
 END

RETURN
END
Go

It can be called like this.

select * from UF_CSVToTable('1,2,3,4,5,6,7,15,55,59,86')

SQL FIDDLE DEMO

Thanks for your idea, I found another way, Instaed of passing names, i mapped all Project ID associated with user. that reduced complexity.. — sk7730, Sep 30 '13 at 08:25

SQL QUERY LIKE & IN TOGETHER

1 Answers1