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: 
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

Now we want to modify this slightly so that it converts a string of words delimited by pipes to a table: 
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

And one more time for a more common purpose where it converts a string of words delimited by commas to a table: 
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