Split a multi-value string into columns

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,"~")

Select allOpen in new window

would return

sa[0]="W"
sa[1]="07938"
(...)

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)

Select allOpen in new window

Any ideas?

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)

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

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

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
 ')

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

Hi Vitor,

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

Thanks

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

You're welcome.
Why are you accepting your last comment as solution?

Excellent Solution

Sorry, I was a bit too quick on the mouse click!