select *from YourTablecross apply dbo.DelimitedSplit8k(replace(YourColumn, ';', ','), ',')

DECLARE @strExample VARCHAR(8000)SET @strExample = 'A,B;C;D,E,F,G,H'SELECT ItemNumber ,ItemFROM dbo.DelimitedSplit8K(REPLACE(@strExample,';',','),',') AS dsk

DECLARE @pString varchar(8000) = '55555;4444,333,22,1';DECLARE @pDelimiter1 char(1) = ',';DECLARE @pDelimiter2 char(2) = ';'; WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) in (@pDelimiter1, @pDelimiter2) ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, Case when ISNULL(NULLIF(CHARINDEX(@pDelimiter1,@pString,s.N1),0)-s.N1,8000) < ISNULL(NULLIF(CHARINDEX(@pDelimiter2,@pString,s.N1),0)-s.N1,8000) then ISNULL(NULLIF(CHARINDEX(@pDelimiter1,@pString,s.N1),0)-s.N1,8000) else ISNULL(NULLIF(CHARINDEX(@pDelimiter2,@pString,s.N1),0)-s.N1,8000) end FROM cteStart s )select * from cteLen;

CREATE TABLE #Strings (strcol VARCHAR(8000));WITH Tally (n) AS ( SELECT TOP 1000 1 FROM sys.all_columns a, sys.all_columns b)INSERT INTO #StringsSELECT REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ',' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ';' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ',' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ';' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ',' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ';' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ',' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ';' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) + ',' + REPLICATE(LEFT('abcdefghijklmnopqrsatuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26),ABS(CHECKSUM(NEWID()))%20) FROM TallyDECLARE @BlackHole VARCHAR(8000)PRINT 'Sean''s suggestion'SET STATISTICS TIME ONselect @BlackHole=Itemfrom #Stringscross apply dbo.DelimitedSplit8k(replace(strcol, ';', ','), ',')SET STATISTICS TIME OFFPRINT 'Dwain''s suggestion'SET STATISTICS TIME ONselect @BlackHole=Itemfrom #StringsCROSS APPLY (SELECT MyString=REPLACE(strcol COLLATE Latin1_General_BIN, ';', ',')) across apply dbo.DelimitedSplit8k(MyString, ',') bSET STATISTICS TIME OFFPRINT 'Sean''s suggestion with COLLATE'SET STATISTICS TIME ONselect @BlackHole=Itemfrom #Stringscross apply dbo.DelimitedSplit8k(replace(strcol COLLATE Latin1_General_BIN, ';', ','), ',')SET STATISTICS TIME OFFDROP TABLE #Strings

Sean's suggestion SQL Server Execution Times: CPU time = 27971 ms, elapsed time = 28411 ms.Dwain's suggestion SQL Server Execution Times: CPU time = 468 ms, elapsed time = 471 ms.Sean's suggestion with COLLATE SQL Server Execution Times: CPU time = 13323 ms, elapsed time = 13567 ms.