Split concatenated field into separate parts

Question

Using SQL Server 2008, I am trying to do something similar to this post regarding splitting a concatenated field into separate parts for normalization purposes.

The problem with the solutions in the linked post is that they only work for delimited values.

The data I am attempting to split would be similar to:

UserID -- ConcatField
1 -- ABC
2 -- DEF

I would want the output to be like:

UserID -- ConcatField
1 -- A
1 -- B
1 -- C
2 -- D
2 -- E
2 -- F

Is there a function to split out these values without the use of a delimiter?

Answer 1

Jeff Moden has an excellent article on Tally Tables at SQL Server Central, which includes a section on splitting out strings like this. The second article listed below is a more in-depth look at strings using the Tally Table.

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://www.sqlservercentral.com/articles/Tally+Table/70738/

(Free subscription required)

Answer 2

Review the available string functions in TSQL: http://msdn.microsoft.com/en-us/library/ms181984.aspx

After loading the raw data in a table, you can separate them relatively easily using the following functions:
* CHARINDEX
* SUBSTRING
* LEN
* LEFT & RIGHT

The actual implementation will of course depend on the data itself, but after splitting the records (maybe by processing them multiple times, first extracting C, then B, then A from 1 -- ABC), you should be good to go.

Answer 3

Is this what you mean?

WITH X AS
(
SELECT 1 AS UserID,'ABC' AS ConcatField UNION ALL
SELECT 2 AS UserID,'DEF' AS ConcatField
), N AS
     (SELECT 1 i
     UNION ALL
     SELECT i+1
     FROM   N
     WHERE  i<1000 /*Or whatever your max string length is*/
     )

SELECT UserID,SUBSTRING(ConcatField,i,1) as ConcatField
 INTO #NewTemporaryTable
 FROM X JOIN N  ON I<= LEN(ConcatField)
OPTION(MAXRECURSION 0)

Gives

UserID      ConcatField
----------- -----------
1           A
1           B
1           C
2           D
2           E
2           F