2. Home
  3. Articles
  4. Database
  5. Transact-SQL
  6. T-SQL functions to convert Strings to Tables

T-SQL functions to convert Strings to Tables

Transact-SQL
Hits: 7893
Applies to:
  • Microsoft SQL Server 2008 R2
  • Microsoft SQL Server 2012
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

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2025 Joel Lipman .com. All Rights Reserved.