CODEWITHSUNDEEP
sql-server-2008
sk7730
sk7730

Reputation: 736

SQL QUERY LIKE & IN TOGETHER

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'

Upvotes: 0

Views: 97

Answers (1)

Chamal
Chamal

Reputation: 1449

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

Upvotes: 1

Related Questions