a table called CLOB contains

ROW_ID (pk)

PARSE_CLOB (where the strings sit)

--=============================================================================

-- Setup

--=============================================================================

USE TestDB --DB that everyone has where we can cause no harm

SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed

DECLARE @StartTime DATETIME --Timer to measure total duration

SET @StartTime = GETDATE() --Start the timer

--=============================================================================

-- Create and populate a Tally table

--=============================================================================

--===== Conditionally drop

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(int,1,1) AS N INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it

-- GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

--===== Display the total duration

SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'

select * from Tally

-----------------------------------------------------------

IF EXISTS (

SELECT *

FROM sysobjects

WHERE id = object_id(N'[dbo].[SPLIT_CLOB]'))

drop table [dbo].[SPLIT_CLOB]

create table SPLIT_CLOB

(ROW_ID nvarchar(15),

SPLIT_CLOB nvarchar(max))

insert into SPLIT_CLOB

SELECT clob.ROW_ID,

SUBSTRING(clob.PARSE_CLOB+',',N+1,CHARINDEX(',',clob.PARSE_CLOB+',',N+1)-N-1) AS Value

FROM dbo.Tally t

CROSS JOIN CLOB clob

WHERE N < LEN(clob.PARSE_CLOB+',')

AND SUBSTRING(clob.PARSE_CLOB+',',N,1) = ','