Print

T-SQL functions to convert Strings to Tables

Applies to:
What?
These were in a solution and I thought I'd note them on my site so I can refer to them more easily.

How?
Let's start with a function that just converts a string of words delimited by spaces to a table:
copyraw 
CREATE FUNCTION dbo.[ufn_StringToTable]
(
        @StringInput VARCHAR(MAX) 
)
RETURNS @OutputTable TABLE ( StringValue VARCHAR(10) )
AS
/*********************************************************************************
** Description          : This function returns a table populated with a row for each string value in the space separated string
** Assumptions          : None
** Inputs               : @StringInput = the space separated string values
** Outputs              : Single table 
** Output Rows          : One row per space separated string value
** Return Values        : None
*********************************************************************************/
BEGIN
    DECLARE @StringValue VARCHAR(10)
    SET @StringInput = RTRIM(LTRIM(@StringInput))

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @StringValue = LEFT(@StringInput, 
                           ISNULL(NULLIF(CHARINDEX(' ', @StringInput) - 1, -1),
                           LEN(@StringInput)))

        SET @StringInput = SUBSTRING(@StringInput,
                           ISNULL(NULLIF(CHARINDEX(' ', @StringInput), 0),
                           LEN(@StringInput)) + 1, LEN(@StringInput))

        SET @StringInput = RTRIM(LTRIM(@StringInput))

        INSERT INTO @OutputTable ( StringValue )
                           VALUES ( @StringValue )
    END

    RETURN
END
  1.  CREATE FUNCTION dbo.[ufn_StringToTable] 
  2.  ( 
  3.          @StringInput VARCHAR(MAX) 
  4.  ) 
  5.  RETURNS @OutputTable TABLE ( StringValue VARCHAR(10) ) 
  6.  AS 
  7.  /********************************************************************************* 
  8.  ** Description          : This function returns a table populated with a row for each string value in the space separated string 
  9.  ** Assumptions          : None 
  10.  ** Inputs               : @StringInput = the space separated string values 
  11.  ** Outputs              : Single table 
  12.  ** Output Rows          : One row per space separated string value 
  13.  ** Return Values        : None 
  14.  *********************************************************************************/ 
  15.  BEGIN 
  16.      DECLARE @StringValue VARCHAR(10) 
  17.      SET @StringInput = RTRIM(LTRIM(@StringInput)) 
  18.   
  19.      WHILE LEN(@StringInput) > 0 
  20.      BEGIN 
  21.          SET @StringValue = LEFT(@StringInput, 
  22.                             ISNULL(NULLIF(CHARINDEX(' ', @StringInput) - 1, -1), 
  23.                             LEN(@StringInput))) 
  24.   
  25.          SET @StringInput = SUBSTRING(@StringInput, 
  26.                             ISNULL(NULLIF(CHARINDEX(' ', @StringInput), 0), 
  27.                             LEN(@StringInput)) + 1, LEN(@StringInput)) 
  28.   
  29.          SET @StringInput = RTRIM(LTRIM(@StringInput)) 
  30.   
  31.          INSERT INTO @OutputTable ( StringValue ) 
  32.                             VALUES ( @StringValue ) 
  33.      END 
  34.   
  35.      RETURN 
  36.  END 

Now we want to modify this slightly so that it converts a string of words delimited by pipes to a table:
copyraw 
CREATE FUNCTION dbo.[ufn_PipeStringToTable]
(
        @StringInput VARCHAR(MAX) 
)
RETURNS @OutputTable TABLE ( StringValue VARCHAR(MAX) )
AS
/*********************************************************************************
** Description          : This function returns a table populated with a row for each string value in the pipe separated string
** Assumptions          : None
** Inputs                       :       @StringInput =  the pipe separated string values
** Outputs                      : Single table 
** Output Rows          : One row per pipe separated string value
** Return Values        : None
*********************************************************************************/
BEGIN

    DECLARE @StringValue VARCHAR(MAX)
    
    -- Trim and remove last pipe character
    SET @StringInput = LEFT(RTRIM(LTRIM(@StringInput)), LEN(RTRIM(LTRIM(@StringInput))) - 1)
    
    WHILE LEN(@StringInput) > 0
    BEGIN
                
        SET @StringValue = LEFT(@StringInput, 
                           ISNULL(NULLIF(CHARINDEX('|', @StringInput) - 1, -1),
                           LEN(@StringInput)))

        SET @StringInput = SUBSTRING(@StringInput,
                           ISNULL(NULLIF(CHARINDEX('|', @StringInput), 0),
                           LEN(@StringInput)) + 1, LEN(@StringInput))

        SET @StringInput = RTRIM(LTRIM(@StringInput))
        
        INSERT INTO @OutputTable ( StringValue )
                         VALUES ( @StringValue )

    END
    
    RETURN
END
  1.  CREATE FUNCTION dbo.[ufn_PipeStringToTable] 
  2.  ( 
  3.          @StringInput VARCHAR(MAX) 
  4.  ) 
  5.  RETURNS @OutputTable TABLE ( StringValue VARCHAR(MAX) ) 
  6.  AS 
  7.  /********************************************************************************* 
  8.  ** Description          : This function returns a table populated with a row for each string value in the pipe separated string 
  9.  ** Assumptions          : None 
  10.  ** Inputs                       :       @StringInput =  the pipe separated string values 
  11.  ** Outputs                      : Single table 
  12.  ** Output Rows          : One row per pipe separated string value 
  13.  ** Return Values        : None 
  14.  *********************************************************************************/ 
  15.  BEGIN 
  16.   
  17.      DECLARE @StringValue VARCHAR(MAX) 
  18.   
  19.      -- Trim and remove last pipe character 
  20.      SET @StringInput = LEFT(RTRIM(LTRIM(@StringInput)), LEN(RTRIM(LTRIM(@StringInput))) - 1) 
  21.   
  22.      WHILE LEN(@StringInput) > 0 
  23.      BEGIN 
  24.   
  25.          SET @StringValue = LEFT(@StringInput, 
  26.                             ISNULL(NULLIF(CHARINDEX('|', @StringInput) - 1, -1), 
  27.                             LEN(@StringInput))) 
  28.   
  29.          SET @StringInput = SUBSTRING(@StringInput, 
  30.                             ISNULL(NULLIF(CHARINDEX('|', @StringInput), 0), 
  31.                             LEN(@StringInput)) + 1, LEN(@StringInput)) 
  32.   
  33.          SET @StringInput = RTRIM(LTRIM(@StringInput)) 
  34.   
  35.          INSERT INTO @OutputTable ( StringValue ) 
  36.                           VALUES ( @StringValue ) 
  37.   
  38.      END 
  39.   
  40.      RETURN 
  41.  END 

And one more time for a more common purpose where it converts a string of words delimited by commas to a table:
copyraw 
CREATE FUNCTION [dbo].[ufn_CommaStringToTable]
(
        @CommaSeparatedValues     VARCHAR(MAX)
)
RETURNS @OutputTable TABLE ( StringValue VARCHAR(255) )
AS
/*********************************************************************************
** Description          : This function returns a table populated with a row for each string value in the comma separated string
** Assumptions          : None
** Inputs               : @CommaSeparatedValues = the comma separated string values
** Outputs              : Single table 
** Output Rows          : One row per comma separated string value
** Return Values        : None
*********************************************************************************/
BEGIN

        DECLARE @IndexOfComma INT
        DECLARE @Value VARCHAR(255)
        DECLARE @StartPos INT
        DECLARE @EndPos INT
        DECLARE @LengthOfString INT
        DECLARE @ReachedEnd Char(1)
 
        SET @StartPos=1
        SET @EndPos=0
        SET @LengthOfString=LEN(@CommaSeparatedValues)
        SET @ReachedEnd='N'
 
        WHILE @ReachedEnd<>'Y'
        BEGIN

                SET @EndPos=CHARINDEX(',',@CommaSeparatedValues,@StartPos)
                IF @EndPos>0
                BEGIN
                        SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @EndPos-@StartPos) 
                        SET @StartPos=@EndPos+1      
                END
                ELSE
                BEGIN
                        SET @ReachedEnd='Y'
                        SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @LengthOfString-(@StartPos-1))
                END
                
                SET @Value = LTRIM(RTRIM(@Value))
                
                IF(@Value<>'') INSERT INTO @OutputTable(StringValue) VALUES(@Value)
        END

        RETURN
END
  1.  CREATE FUNCTION [dbo].[ufn_CommaStringToTable] 
  2.  ( 
  3.          @CommaSeparatedValues     VARCHAR(MAX) 
  4.  ) 
  5.  RETURNS @OutputTable TABLE ( StringValue VARCHAR(255) ) 
  6.  AS 
  7.  /********************************************************************************* 
  8.  ** Description          : This function returns a table populated with a row for each string value in the comma separated string 
  9.  ** Assumptions          : None 
  10.  ** Inputs               : @CommaSeparatedValues = the comma separated string values 
  11.  ** Outputs              : Single table 
  12.  ** Output Rows          : One row per comma separated string value 
  13.  ** Return Values        : None 
  14.  *********************************************************************************/ 
  15.  BEGIN 
  16.   
  17.          DECLARE @IndexOfComma INT 
  18.          DECLARE @Value VARCHAR(255) 
  19.          DECLARE @StartPos INT 
  20.          DECLARE @EndPos INT 
  21.          DECLARE @LengthOfString INT 
  22.          DECLARE @ReachedEnd Char(1) 
  23.   
  24.          SET @StartPos=1 
  25.          SET @EndPos=0 
  26.          SET @LengthOfString=LEN(@CommaSeparatedValues) 
  27.          SET @ReachedEnd='N' 
  28.   
  29.          WHILE @ReachedEnd<>'Y' 
  30.          BEGIN 
  31.   
  32.                  SET @EndPos=CHARINDEX(',',@CommaSeparatedValues,@StartPos) 
  33.                  IF @EndPos>0 
  34.                  BEGIN 
  35.                          SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @EndPos-@StartPos) 
  36.                          SET @StartPos=@EndPos+1 
  37.                  END 
  38.                  ELSE 
  39.                  BEGIN 
  40.                          SET @ReachedEnd='Y' 
  41.                          SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos, @LengthOfString-(@StartPos-1)) 
  42.                  END 
  43.   
  44.                  SET @Value = LTRIM(RTRIM(@Value)) 
  45.   
  46.                  IF(@Value<>'') INSERT INTO @OutputTable(StringValue) VALUES(@Value) 
  47.          END 
  48.   
  49.          RETURN 
  50.  END 
Category: Transact-SQL :: Article: 579