T-SQL Split row on delimiter

CREATE FUNCTION [dbo].[udf_PivotParameters]
    (
      @ParamaterList VARCHAR(MAX),
      @Delimiter CHAR(1)
    )
RETURNS @ReturnList TABLE
    (
      FieldValue VARCHAR(MAX)
    )
AS BEGIN
    DECLARE @ArrayList TABLE
        (
          FieldValue VARCHAR(MAX)
        )
    DECLARE @Value VARCHAR(MAX)
    DECLARE @CurrentPosition INT
 
    SET @ParamaterList = LTRIM(RTRIM(@ParamaterList))
        + CASE WHEN RIGHT(@ParamaterList, 1) = @Delimiter THEN ''
               ELSE @Delimiter
          END
    SET @CurrentPosition = ISNULL(CHARINDEX(@Delimiter, @ParamaterList, 1), 0)  

    IF @CurrentPosition = 0
        INSERT  INTO @ArrayList ( FieldValue )
                SELECT  @ParamaterList
    ELSE
        BEGIN
            WHILE @CurrentPosition > 0
                BEGIN
                    SET @Value = LTRIM(RTRIM(LEFT(@ParamaterList,
                                                  @CurrentPosition - 1))) --make sure a value exists between the delimiters
                    IF LEN(@ParamaterList) > 0
                        AND @CurrentPosition <= LEN(@ParamaterList)
                        BEGIN
                            INSERT  INTO @ArrayList ( FieldValue )
                                    SELECT  @Value
                        END
                    SET @ParamaterList = SUBSTRING(@ParamaterList,
                                                   @CurrentPosition
                                                   + LEN(@Delimiter),
                                                   LEN(@ParamaterList))
                    SET @CurrentPosition = CHARINDEX(@Delimiter,
                                                     @ParamaterList, 1)
                END
        END
    INSERT  @ReturnList ( FieldValue )
            SELECT  FieldValue
            FROM    @ArrayList
    RETURN
   END

The function accepts the list of values, along with the delimiting character to separate the list. Then the function loops through the array list, inserting everything between the delimiting characters into a table variable, which is eventually returned as the output from the function. Because this is a table-valued function, you need to return the values from a SELECT statement. This code snippet shows you how:

DECLARE @ArrayList VARCHAR(MAX)    

SET @ArrayList = 'tim,zach,chris,wendi,brad'

SELECT * FROM dbo.udf_PivotParameters(@ArrayList, ',')

I can do that if it's the only thing I'm looking to split, but I'm also looking to repeat the other information.

My query is a bit more complex than the example and is using a bunch of joins to get infromation from different tables.  I need to repeat ALL the other information.

Attached is a sample join of the pubs DB.

I'm trying to split pr_info on any commas found while repeating the pub_name where it came from.

Again my query is a bit more complex and I need to repeat ALL fields.

select p.pub_name, p1.pr_info from publishers p join pub_info p1 on p.pub_id = p1.pub_id

Select allOpen in new window

sql 2000 . . . but that's good to know for when we upgrade.  Any chance of doing it in 2000?

Sure...there is a function in this post you can use...it is setup as a scalar function rather than a table valued one.

https://www.experts-exchange.com/questions/23448200/Pass-multiline-text-parameter-to-stored-procedure.html

Looks like the function returns a table, not scalar?  Can you post the function?

I see what you're saying...there really isn't a good way to do it in 2000, unfortunately.  

Actually I was able to find it, I'm going to request this question be reduced to 125 for a partial answer.

http://weblogs.sqlteam.com/peterl/archive/2007/10/15/Cross-apply-in-SQL-Server-2000.aspx

select   SUBSTRING(',' + YOUR_COLUMN + ',', n.Number + 1, CHARINDEX(',', ',' + YOUR_COLUMN + 
',', n.Number + 1) - n.Number - 1) AS [YOUR_COLUMN]
INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + YOUR_COLUMN + ',', n.Number, 1) = ','
WHERE           n.Type = 'p'
                AND n.Number > 0 
                AND n.Number < LEN(',' + YOUR_COLUMN + ',')

Select allOpen in new window

OK

You can also do a split by accepting your answer and giving partial points to someone else.