CODEWITHSUNDEEP
sqlsql-server
Asim Sajjad
Asim Sajjad

Reputation: 2534

Comparing Comma Separate Value with Comma Separated Values SQL

I have a table A which contain column "ColumnName" (it will contain the comma separated integer values ) and I have store procedure which take parameter which is also comma separated integer values. For example I have values in table "101,102,103" and "103,104,105" and User input is "101,104" it should return 2 record. how can I achieve this? Need SQL statements

Upvotes: 2

Views: 1871

Answers (3)

Morten
Morten

Reputation: 414

This should do it:

CREATE PROCEDURE GetMatches(@input varchar (100))
AS
BEGIN

    WITH CTE AS
    (
        SELECT value AS number
        FROM STRING_SPLIT(@input, ',') 
    )
    SELECT CTE.number, A.ColumnName
    FROM A
    INNER JOIN CTE
    ON ',' + A.ColumnName + ',' LIKE '%,' + CTE.number + ',%';

END

You can test the stored procedure like this:

EXEC dbo.GetMatches @input = '101,104';

Upvotes: 1

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

CREATE FUNCTION [dbo].[fnParseArray] (@Array VARCHAR(MAX),@separator CHAR(1))

RETURNS @T Table (col1 varchar(50))

AS 

BEGIN

-- @Array is the array we wish to parse

 -- @Separator is the separator charactor such as a comma

  DECLARE @separator_position INT -- This is used to locate each separator character

  DECLARE @array_value VARCHAR(MAX) -- this holds each array value as it is returned

 -- For my loop to work I need an extra separator at the end. I always look to the

  -- left of the separator character for each array value



  SET @array = @array + @separator



 -- Loop through the string searching for separtor characters

  WHILE PATINDEX('%' + @separator + '%', @array) <> 0 

  BEGIN

-- patindex matches the a pattern against a string

SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)

SELECT @array_value = LEFT(@array, @separator_position - 1)

-- This is where you process the values passed.

INSERT into @T VALUES (@array_value)    

-- Replace this select statement with your processing

-- @array_value holds the value of this element of the array

-- This replaces what we just processed with and empty string

SELECT @array = STUFF(@array, 1, @separator_position, '')

END

RETURN 

 END


select * from [dbo].[fnParseArray]('a,b,c,d',',')

Upvotes: 0

Killer Queen
Killer Queen

Reputation: 756

If you need help, explanations, let me know :)

DECLARE 
    @input  VARCHAR(MAX) = '101,104', 
    @separator CHAR(1) =',',
    @separatorPosition INT,
    @value VARCHAR(MAX),
    @start INT = 1

DECLARE @split_input TABLE(Value VARCHAR(MAX))
DECLARE @tmp TABLE (st VARCHAR(50))

INSERT INTO @tmp VALUES ('101,102,103')
INSERT INTO @tmp VALUES ('103,104,105')
INSERT INTO @tmp VALUES ('106,107,108')

SET @separatorPosition = CHARINDEX(@separator, @input)


 IF @separatorPosition = 0
 BEGIN
     INSERT INTO @split_input
     VALUES
     (
         @input
     )
     RETURN
 END

 SET @input = @input + @separator

 WHILE @separatorPosition > 0
 BEGIN
     SET @value = SUBSTRING(@input, @start, @separatorPosition - @start)

     IF (@value <> '')
         INSERT INTO @split_input
         VALUES
         (
             @value
         )

     SET @start = @separatorPosition + 1
     SET @separatorPosition = CHARINDEX(@separator, @input, @start)
 END


 SELECT tmp.st FROM @tmp AS tmp INNER JOIN @split_input AS split ON tmp.st LIKE '%' + split.Value + '%'

Upvotes: 0

Related Questions