How to convert rows data to columns if the input data is separated by some separator (,;/#< etc)?

607    Asked by JacobRutherford in SQL Server , Asked on Nov 5, 2019
Answered by Jacob Rutherford

We can create a multi-line table valued function which will return a table on passing to parameter in input (one will be the string which needs to be converted to string and other will be the separator which the string is separated.

Below is the code for the same:

CREATE FUNCTION [DBO].SPLIT_DELIMITED_STRING (@P_InputString VARCHAR(MAX),

                                              @Separator CHAR(1))

RETURNS @O_table TABLE(VALUE VARCHAR(MAX))

AS

  BEGIN

      DECLARE @Separator_POSITION INT = CHARINDEX(@Separator , @P_InputString),

              @VALUE VARCHAR(MAX),

              @STARTPOSITION INT = 1

   IF @Separator_POSITION = 0

        BEGIN

            INSERT INTO @O_table

            VALUES (@P_InputString)

          RETURN

        END

   SET @P_InputString = @P_InputString + @Separator

   WHILE @Separator_POSITION > 0

        BEGIN

            SET @VALUE = SUBSTRING(@P_InputString, @STARTPOSITION, @Separator_POSITION - @STARTPOSITION)

          IF( @VALUE <> '' )

              INSERT INTO @O_table

              VALUES (@VALUE)

          SET @STARTPOSITION = @Separator_POSITION + 1

            SET @Separator_POSITION = CHARINDEX(@Separator , @P_InputString, @STARTPOSITION)

        END

   RETURN

  END

Input: select * from SPLIT_DELIMITED_STRING ('abc,defghi,jklm,nop,qrstuv,wxyz',',')

Output:




Your Answer

Interviews

Parent Categories