Split a multi-value string into columns

Hi all,

I have a table values returned in a SELECT statement which look something like this:

W~07938~1~0~1~10~260
Q~15709~0~0~1~10~40
Q~15709~0~0~1~10~190
M~RPA0214-FS-BTM~0~0~3~6~40

Always the same number of values (7) and always seperated by a ~

What I would like to do is split all of these values into 7 seperate columns which I can then use in a join with another table.

Any ideas good people?

Thanks
LVL 3
FMabeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frankhelkCommented:
If you could use some .NET language, you could use the Split() method. It would break up a list of delimited substrings into an array os strings.

It's a top level function in VB, and implemented in the string class as String.Split().

Dim s as String;
Dim sa as String[];
s = "W~07938~1~0~1~10~260"
sa = s.Split(s,"~")

Open in new window


would return

sa[0]="W"
sa[1]="07938"
(...)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can create a temporary table and insert the values there:
CREATE TABLE #NewTable
(Col1 VARCHAR(100),
Col2 VARCHAR(100),
Col3 VARCHAR(100),
Col4 VARCHAR(100),
Col5 VARCHAR(100),
Col6 VARCHAR(100),
Col7 VARCHAR(100))

DECLARE @InsertRows VARCHAR(MAX)

SELECT @InsertRows = STUFF((SELECT 'INSERT INTO #NewTable VALUES('''+Replace(MultiValueColumnNameHere,'~',''',''')+''');'
				FROM OriginalTableNameHere
				FOR XML Path('')),1,0,'')
EXEC (@InsertRows)

SELECT * 
FROM #NewTable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FMabeyAuthor Commented:
Hi Vitor,

Thanks for your response. When I try your solution I get the following error:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

(0 row(s) affected)


My code is as follows:
CREATE TABLE #NewTable
(Col1 VARCHAR(100),
Col2 VARCHAR(100),
Col3 VARCHAR(100),
Col4 VARCHAR(100),
Col5 VARCHAR(100),
Col6 VARCHAR(100),
Col7 VARCHAR(100))

DECLARE @InsertRows VARCHAR(MAX)

SELECT @InsertRows = STUFF((SELECT 'INSERT INTO #NewTable VALUES('''+Replace(PRIMARY_KEY,'~',''',''')+''');'
				FROM HISTORY_DATA
				WHERE CREATE_DATE >= (SELECT CONVERT (VARCHAR(10), Getdate() - 6, 101)) 
       AND CREATE_DATE <= (SELECT CONVERT (VARCHAR(10), Getdate(), 101)) 
				FOR XML Path('')),1,0,'')
EXEC (@InsertRows)

Open in new window


Any ideas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You sure that's really 7 columns?
You can verify the INSERT statements by printing the variable value before running the command:
SELECT @InsertRows
--EXEC (@InsertRows)
0
frankhelkCommented:
Hmmm - maybe just a typo, but the data quoted in the question shows 9 data values in the last line instead of 7 in the other rows ...
0
Scott PletcherSenior DBACommented:
Use a very high-performing split such as Delimited8KSplit.  I tried posting the code for it here but it won't "Submit", e-e apparently thinks it's some type of sql injection, but you should be able to Google the code.

SELECT
    row_key_col,
    MAX(CASE WHEN ItemNumber = 1 THEN Item END) AS value_1,
    MAX(CASE WHEN ItemNumber = 2 THEN Item END) AS value_2,
    MAX(CASE WHEN ItemNumber = 3 THEN Item END) AS value_3,
    MAX(CASE WHEN ItemNumber = 4 THEN Item END) AS value_4,
    MAX(CASE WHEN ItemNumber = 5 THEN Item END) AS value_5,
    MAX(CASE WHEN ItemNumber = 6 THEN Item END) AS value_6,
    MAX(CASE WHEN ItemNumber = 7 THEN Item END) AS value_7
FROM table_name
CROSS APPLY dbo.Delimited8KSplit ( multivalue_column, '~' ) ds
GROUP BY
    row_key_col
0
Scott PletcherSenior DBACommented:
Here's the function code; sorry I can't post it more cleanly, but the site won't allow it.  Just copy, paste and run this code, then the output from it can be used to create the function.

 SELECT REVERSE(
 '
 l neLetc MORF  

 )1L.l ,1N.l ,gnirtSp@(GNIRTSBUS = metI        

 ,)1N.l YB REDRO(REVO )(REBMUN_WOR = rebmuNmetI TCELES

 .dnuof si retimiled on nehw tnemele lanif eht rof htgnel eht seldnah obmoc FILLUN/LLUNSI ehT .tilps lautca eht oD =--

 )                

 s tratSetc MORF                  

 )0008,1N.s-)0,)1N.s,gnirtSp@,retimileDp@(XEDNIRAHC(FILLUN(LLUNSI                        

 ,1N.s TCELES                

 )gnirtsbus ni esu rof( htgnel dna trats nruteR --(SA )1L,1N(neLetc

 ,)                

 retimileDp@ = )1,N.t,gnirtSp@(GNIRTSBUS EREHW t yllaTetc MORF 1+N.t TCELES                

 LLA NOINU 1 TCELES                

 )retimiled hcae rof ecno tsuj "tnemele" hcae fo noitisop gnitrats( 1+N snruter sihT --( SA )1N(tratSetc

 ,)                

 4E MORF ))LLUN TCELES( YB REDRO( REVO )(REBMUN_WOR ))0,)gnirtSp@(HTGNELATAD(LLUNSI( POT TCELES                

 "snurrevo" latnedicca fo noitneverp dna niag ecnamrofrep a htob rof --                    

 tnorf pu thgir swor fo rebmun eht stimil dna ETC "esab" eht sedivorp sihT --( SA )N(yllaTetc

 xam swor 000,01 ro 4+E01-- ,)b 2E ,a 2E MORF 1 TCELES( SA )N(4E      

 swor 001 ro 2+E01-- ,)b 1E ,a 1E MORF 1 TCELES( SA )N(2E      

 swor 01 ro 1+E01--                          ,)                

 1 TCELES LLA NOINU 1 TCELES LLA NOINU 1 TCELES LLA NOINU 1 TCELES                

 LLA NOINU 1 TCELES LLA NOINU 1 TCELES LLA NOINU 1 TCELES                

 LLA NOINU 1 TCELES LLA NOINU 1 TCELES LLA NOINU 1 TCELES                

 ( SA )N(1E HTIW  

 )0008(RAHCRAV revoc ot hguone --    

 ...000,01 ot pu 1 morf seulav secudorp "elbaT yllaT" nevirD ETC "enilnI" --

 NRUTER

 SA GNIDNIBAMEHCS HTIW ELBAT SNRUTER

 !tilps eht od ot emit eht elbuod tsael ta lliw epyt atad )xam( :ETON--

 ))1(RAHC retimileDp@ ,)0008(RAHCRAV gnirtSp@(        

 sretemarap O/I enifeD --

 ]K8tilpSdetimileD[.]obd[ NOITCNUF ETAERC
 ')
0
Gerald ConnollyCommented:
Scott, of course you can enter code, if you haven't noticed the code button above this box just surround your code with the tags code & /code both tags inside square brackets.

Any way I don't know how you did it but the code you have posted is back-to-front
0
FMabeyAuthor Commented:
Hi Vitor,

I've found the culrprit. There was a rogue value in the table. Amended that and your script works well.

Thanks
0
FMabeyAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for FMabey's comment #a40667902

for the following reason:

Excellent solution
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're welcome.
Why are you accepting your last comment as solution?
0
FMabeyAuthor Commented:
Excellent Solution
0
FMabeyAuthor Commented:
Sorry, I was a bit too quick on the mouse click!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.