CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008function
Sesame
Sesame

Reputation: 3410

Split concatenated field into separate parts

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?

Upvotes: 0

Views: 1660

Answers (3)

Martin Smith
Martin Smith

Reputation: 453628

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

Edit: Though as Jeff Moden points out in the comments the performance of recursive CTEs is far from great so you would be best off creating a permanent numbers table using one of the techniques from here and then using

SELECT  UserID ,
        SUBSTRING(ConcatField, i, 1) AS ConcatField
INTO    #NewTemporaryTable
FROM    YourTable
        JOIN Numbers ON Number <= LEN(ConcatField)

Upvotes: 0

Cahit
Cahit

Reputation: 2534

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.

Upvotes: 0

user114600
user114600

Reputation:

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)

Upvotes: 1

Related Questions