Need to search each and every string in the comma delimited string input (AND Condition)

• saichaitanya.cloucom

  Grasshopper

  Points: 16

  More actions

  March 10, 2015 at 11:15 pm

  #315362

  I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.

  Example:

  DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'

  SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'

  As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.

  Please suggest.

• psingla

  Hall of Fame

  Points: 3840

  More actions

  March 11, 2015 at 12:23 am

  #1783132

  This query will not return any result ever.

  I think you mean OR instead of AND in the where clause...

  Pramod
  SQL Server DBA | MCSE SQL Server 2012/2014
  in.linkedin.com/in/pramodsingla/
  http://pramodsingla.wordpress.com/

• saichaitanya.cloucom

  Grasshopper

  Points: 16

  More actions

  March 11, 2015 at 1:34 am

  #1783137

  Hi Pramod,

  I know it wouldn't get me a result whatever but that's what the scenario asks me for.

  I just need to find a way to implement that.

• PRAMANA.DBA

  SSCertifiable

  Points: 5507

  More actions

  March 11, 2015 at 1:48 am

  #1783141

  Hi

  use this below code to get indivual value from given string

  ---------

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  Create FUNCTION [dbo].[CommaSeperateValuesToTable] ( @CommaseperateString VARCHAR(Max), @Delimiter nvarchar(1))

  RETURNS @Result TABLE ( [Value] VARCHAR(50) )

  AS

  BEGIN

  DECLARE @String VARCHAR(Max)

  WHILE LEN(@CommaseperateString) > 0

  BEGIN

  SET @String = LEFT(@CommaseperateString,

  ISNULL(NULLIF(CHARINDEX(@Delimiter, @CommaseperateString) - 1, -1),

  LEN(@CommaseperateString)))

  SET @CommaseperateString = SUBSTRING(@CommaseperateString,

  ISNULL(NULLIF(CHARINDEX(@Delimiter, @CommaseperateString), 0),

  LEN(@CommaseperateString)) + 1, LEN(@CommaseperateString))

  INSERT INTO @Result ( [Value] )

  VALUES ( @String )

  END

  RETURN

  END

  ------------

  ----for execution

  DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'

  SELECT * FROM TABLE WHERE titleName in (select value from dbo.CommaSeperateValuesToTable(@StrInput,','))