There is a cute solution which i borrowed from some articles about Tallys on this Web-Site.

Check this out: http://www.sqlservercentral.com/articles/T-SQL/63003/

Here is how i implemented:

1. Create a Tally table. This is simple table with one Column N, which contains (for instance) numbers from 1 to 11000.

--Create a Tally Table

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

--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC

2. Create a parsing function:

create function [dbo].[FUNC_TBL_TEXT_IN_COL]

(

@Numbersvarchar(max)

,@Delimiterchar

) returns table

as

returnSELECTSUBSTRING(@Delimiter+@Numbers+@Delimiter,N+1,CHARINDEX(@Delimiter,@Delimiter+@Numbers+@Delimiter,N+1)-N-1) as X

FROMdbo.Tally

WHEREN < LEN(@Delimiter+@Numbers+@Delimiter)

AND SUBSTRING(@Delimiter+@Numbers+@Delimiter,N,1) = @Delimiter

3. Run an example:

selectX

from[dbo].[FUNC_TBL_TEXT_IN_COL]

(

'Jones,Schneider,68,whatever',','

)

Result is:

X

----

Jones

Schneider

68

whatever