How to Split Comma Separated String Inside Stored Procedure

How to separate (split) string with comma in SQL Server stored procedure

If you pass the comma separated (any separator) string to store procedure and use in query so must need to spit that string and then you will use it.

Below have example:

DECLARE @str VARCHAR(500) = 'monday,tuesday,thursday'
CREATE TABLE #Temp (tDay VARCHAR(100))
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
    IF CHARINDEX(',',@str) > 0
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
    ELSE
        BEGIN
        SET  @TDay = @str
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END

 SELECT * 
 FROM tblx 
 WHERE days IN (SELECT tDay FROM #Temp)

How to split comma separated string inside stored procedure?

Here a sample how to split the string and write the sub-strings into a table:

create procedure SPLIT_STRING (
  AINPUT varchar(8192))
as
declare variable LASTPOS integer;
declare variable NEXTPOS integer;
declare variable TEMPSTR varchar(8192);
begin
  AINPUT = :AINPUT || ',';
  LASTPOS = 1;
  NEXTPOS = position(',', :AINPUT, LASTPOS);
  while (:NEXTPOS > 1) do
  begin
    TEMPSTR = substring(:AINPUT from :LASTPOS for :NEXTPOS - :LASTPOS);
    insert into new_table("VALUE") values(:TEMPSTR);
    LASTPOS = :NEXTPOS + 1;
    NEXTPOS = position(',', :AINPUT, LASTPOS);
  end
  suspend;
end

How to separate string by comma using SQL Server?

You can try splitting using xml path as below:

Declare @delimiter nvarchar(max) = ','
Declare @str nvarchar(max) = '12,22,45,66'

Declare @xml as xml
        Select @xml = CAST('<x>' + REPLACE((SELECT REPLACE(@str,@delimiter,'$$$SSText$$$') AS [*] FOR XML PATH('')),'$$$SSText$$$','</x><x>')+ '</x>' AS XML) 

--select @xml
Select y.value(N'text()[1]', N'nvarchar(MAX)') as value 
FROM @xml.nodes(N'x') as x(y) 

If you are in SQL Server >= 2016 you can use STRING_SPLIT() as below:

Select * from string_split('12,22,45,66',',')

Remove a value from a comma separated string in sql stored procedure

No need for loops (please take a peek at Larnu's suggestion for your parse/split function)

That said, consider the following

Example

Declare @S varchar(max) = '123456,2345678,123567,2345890'

Declare @Zap varchar(50)='123456'

Select reverse(stuff(reverse(stuff(replace(','+@S+',',','+@Zap+',',','),1,1,'')),1,1,''))

Returns

2345678,123567,2345890

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How to split comma separated text in MySQL stored procedure

This is simple as hell for MySQL:

SELECT * FROM table WHERE FIND_IN_SET(table.id, commaSeparatedData);

Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

---

Related Topics