IF OBJECT_ID('tempdb..#Strings') IS NOT NULL
DROP TABLE #Strings

CREATE TABLE #Strings (ID INT IDENTITY, MyString VARCHAR(MAX))

;WITH Tally (n) AS (
    SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #Strings
SELECT CAST((
    SELECT CAST(n AS VARCHAR(5)) + ';' + CAST(40000+n AS VARCHAR(6)) + '|'
    FROM Tally
    FOR XML PATH('')) AS VARCHAR(MAX))
FROM Tally

IF OBJECT_ID('tempdb..#Strings') IS NOT NULL
DROP TABLE #Strings

CREATE TABLE #Strings (ID INT IDENTITY, MyString VARCHAR(MAX))

DECLARE @X INT
SET @X = 1

WHILE @X <= 100
    BEGIN

        INSERT INTO #Strings
        SELECT CAST((
            SELECT TOP(100)
                dbo.svfGenerateUniqueCode
                    (0,ISNULL(NULLIF(FLOOR(CAST(RAND(t1.N*10000000) AS DECIMAL(18,4))*10),0),10),'')
                + ';' + 
                dbo.svfGenerateUniqueCode
                    (0,ISNULL(NULLIF(FLOOR(CAST(RAND((t1.N+100)*10000000) AS DECIMAL(18,4))*10),0),10),'')
                 + '|'
            FROM 
                dbo.Tally AS t1
            FOR XML PATH('')) AS VARCHAR(MAX))
            
        SET @X = @X+1
    
    END

DECLARE @Holder1 VARCHAR(MAX), @Holder2 VARCHAR(MAX)

PRINT 'DelimitedSplit8K (Lynn Pettis)'
SET STATISTICS TIME ON 
SELECT
    @Holder1 = MAX(CASE ds2.ItemNumber
                     WHEN 1 THEN ds2.Item
                     ELSE ''
                   END)
   , --as field1,
    @Holder2 = MAX(CASE ds2.ItemNumber
                     WHEN 2 THEN ds2.Item
                     ELSE ''
                   END) --as field2
FROM
    #Strings
CROSS APPLY dbo.DelimitedSplit8K(MyString,'|') ds1
CROSS APPLY dbo.DelimitedSplit8K(ds1.Item,';') ds2
WHERE
    ds1.Item <> ''
GROUP BY
    ID
   ,ds1.ItemNumber ;
SET STATISTICS TIME OFF 


PRINT 'PatternSplitCM (Dwain.C)'
SET STATISTICS TIME ON 
SELECT
    @Holder1 = MAX(CASE ItemNumber % 4
                     WHEN 1 THEN Item
                   END)
   ,@Holder2 = MAX(CASE ItemNumber % 4
                     WHEN 3 THEN Item
                   END)
FROM
    #Strings
CROSS APPLY PatternSplitCM(MyString,'[0-9]')
WHERE
    [Matched] = 1
GROUP BY
    ID
   ,ItemNumber / 4
--OPTION (MAXDOP 1)
SET STATISTICS TIME OFF


PRINT 'XML (Mister Magoo)'
GO
SET STATISTICS TIME ON
GO

DECLARE @Holder1 VARCHAR(MAX), @Holder2 VARCHAR(MAX)

SELECT
    @Holder1 = nd.value('./@a','varchar(200)')
   ,@Holder2 = nd.value('./@b','varchar(200)')
FROM
    (
     SELECT
        CAST('<c a="' + REPLACE(REPLACE(MyString,'|','"/><c a="'),';','" b="') + '"/>' AS XML).query('.')
     FROM
        #Strings
    FOR
     XML PATH('')
        ,TYPE
    ) AS src (nds)
CROSS APPLY nds.nodes('c') AS x (nd)
WHERE
    nd.exist('./@b') = 1

GO

SET STATISTICS TIME OFF 
GO



PRINT 'DelimitedSplit_XML2 (Steven Willis)'
GO
SET STATISTICS TIME ON
GO

DECLARE
        @Holder3 VARCHAR(MAX)
       ,@Holder4 VARCHAR(MAX)

SELECT
    @Holder3 = Col1
   ,@Holder4 = Col2
FROM
    (
     SELECT
        ID
       ,dsk1.Col1
       ,dsk1.Col2
     FROM
        #Strings a
     CROSS APPLY dbo.tvfDelimitedSplitXML2(MyString,'|',';') AS dsk1
    ) r1
GO

SET STATISTICS TIME OFF 
GO

/* This function will also split 1-dimensional arrays if Delimiter2 is left blank */

CREATE FUNCTION [dbo].[tvfDelimitedSplitXML2]
    (
     @InputString VARCHAR(MAX)
    ,@Delimiter1 CHAR(1)
    ,@Delimiter2 CHAR(1)
    )
RETURNS @Elements TABLE 
    (
     ItemNumber INT IDENTITY(1,1) NOT NULL
    ,Col1 VARCHAR(MAX) NULL
    ,Col2 VARCHAR(MAX) NULL
    ,PRIMARY KEY (ItemNumber)
    )
WITH SCHEMABINDING
AS
BEGIN
    
    DECLARE
        @X VARCHAR(MAX)
       ,@XML XML 
       ,@NumElements INT 
       ,@Counter INT
         
    
    IF RIGHT(@InputString,1) = @Delimiter1
        SET @InputString = LEFT(@InputString,LEN(@InputString)-1)
    IF LEFT(@InputString,1) = @Delimiter1
        SET @InputString = RIGHT(@InputString,LEN(@InputString)-1)


    IF NULLIF(@Delimiter2,'') IS NULL
        BEGIN
        
            -- Parse the 1-dimensional delimited string array
            SET @X = '<root><s>' + REPLACE(@InputString,@Delimiter1,'</s><s>')+'</s></root>'

            -- Convert the string into XML
            SET @XML = CONVERT(XML,@X)

            -- Select the rows from the array
            INSERT INTO @Elements (Col1)
            SELECT 
                T.c.value('.','VARCHAR(MAX)') AS [Value] FROM @XML.nodes('/root/s') T (c)    
        
        END
    ELSE
        BEGIN

            -- Parse the 2-dimensional delimited string array

            SET @X = '<s1>' + REPLACE(@InputString,@Delimiter1,'</s2><s1>')+'</s2>'
            SET @X = REPLACE(@X,@Delimiter2,'</s1><s2>')
            SET @X = '<root>'+@X+'</root>'


            -- Convert the string into XML
            SET @XML = CONVERT(XML,@X)


            -- Insert for each pair in the array

            INSERT INTO @Elements
            SELECT
                Col1
               ,Col2
            FROM
                (SELECT 
                    ROW_NUMBER() OVER(ORDER BY Col1) AS RowID1
                    ,Col1
                 FROM
                     (SELECT T.c.value('.','VARCHAR(MAX)') AS [Col1] FROM @XML.nodes('/root/s1') T (c)) AS Col1
                ) AS Col1
                INNER JOIN 
                (SELECT 
                    ROW_NUMBER() OVER(ORDER BY Col2) AS RowID2
                    ,Col2
                 FROM
                     (SELECT T.c.value('.','VARCHAR(MAX)') AS [Col2] FROM @XML.nodes('/root/s2') T (c)) AS Col2
                ) AS Col2
                ON Col1.RowID1 = Col2.RowID2
                
        END
                
    RETURN 
    
    /*
    Usage:
    
    SELECT * FROM dbo.tvfDelimitedSplitXML2(
        '30;38469|31;38470|32;38471|33;38472|34;38473|35;38474|36;38475|37;38476|38;38477|'
        ,'|'
        ,';'
        ) 
    WHERE ItemNumber > 0
    
    SELECT * FROM dbo.tvfDelimitedSplitXML2(
        'Uu6xS;e7Gd|cQm;J|DQ;BgU|F4;f|UqEqZx;fWK|E0k1WRan;h|Pz;1fd|wjZchH;meYAb|w1U7uhj;rusCg'
        ,'|'
        ,';'
        ) 
    WHERE ItemNumber > 0 

    SELECT * FROM dbo.tvfDelimitedSplitXML2(
        'Element01,Element02,Element03,Element04,Element05'
        ,',','') 
    WHERE ItemNumber > 0
        
    */
    
END

--Used to pass 'NEWID()' into the function below

CREATE VIEW [dbo].[vwRandomGUID]
AS
SELECT
    NEWID() AS RandomGUID
GO

--Generates random values

CREATE FUNCTION [dbo].[svfGenerateUniqueCode]
    (
     @CodeMinLength INT
    ,@CodeMaxLength INT
    ,@SpecialChar VARCHAR(50)
    )
RETURNS VARCHAR(100)
AS
BEGIN

    DECLARE @Code VARCHAR(100)

    DECLARE @CodeData TABLE
        (
         CodeChar VARCHAR(1)
        )
        
    DECLARE @Num TABLE
        (
         Digit INT NOT NULL
         PRIMARY KEY CLUSTERED
        )
        
    IF @CodeMaxLength <= @CodeMinLength
        SET @CodeMaxLength = @CodeMinLength + 1


    INSERT INTO @Num
            (
             Digit
            )
            SELECT
                Digit = 0
            UNION ALL
            SELECT
                Digit = 1
            UNION ALL
            SELECT
                Digit = 2
            UNION ALL
            SELECT
                Digit = 3
            UNION ALL
            SELECT
                Digit = 4
            UNION ALL
            SELECT
                Digit = 5
            UNION ALL
            SELECT
                Digit = 6
            UNION ALL
            SELECT
                Digit = 7
            UNION ALL
            SELECT
                Digit = 8
            UNION ALL
            SELECT
                Digit = 9
            ORDER BY
                1

    INSERT INTO @CodeData
            (
             CodeChar
            )
            SELECT
                CodeChar = SUBSTRING(b.Characters,a.RAND_INT % b.MOD,1)
            FROM
                (
                 SELECT
                    aa.Number
                   ,RAND_INT = ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT RandomGUID FROM dbo.vwRandomGUID))))
                 FROM
                    (
                     SELECT Number = a.Digit + (b.Digit * 10) FROM @Num a CROSS JOIN @Num b
                    ) aa
                ) a
            CROSS JOIN 
                (
                SELECT
                    MOD = LEN(bb.Characters) - 1
                   ,bb.Characters
                FROM
                    (
                     SELECT
                        Characters = 
                        'ABCDEFGHJKLMNPQURSUVWXYZ'
                        + 'abcdefghjkmnpqursuvwxyz'
                        + '0123456789'
                        + @SpecialChar
                    ) bb
               ) b
            ORDER BY
                (SELECT RandomGUID FROM dbo.vwRandomGUID)

    SELECT
        @Code = ''

    SELECT
        @Code = @Code + CodeChar
    FROM
        @CodeData

    SELECT
        @Code = 
        -- Random length from MIN to MAX Characters
        SUBSTRING(@Code,1,@CodeMinLength + (ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT RandomGUID FROM dbo.vwRandomGUID))))) % (@CodeMaxLength - @CodeMinLength + 1))

    SET @Code = NULLIF(LTRIM(RTRIM(@Code)),'')

    RETURN @Code

END
GO