how to split the variable with '~' symbol and need to store in temp table like below .

Question

how to split the variable with '~' symbol and need to store in temp table like below .

s.chandrahasan

SSC Eights!

Points: 858
More actions
November 15, 2013 at 12:47 am

#300267

HI All ,
declare @var varchar(max)
set @var='F1-f2~F3-F4~F5-F6~F8-F9~F3-F2~F8-F7'
how to split this variable with '~' and that need to store in temp table like below .
Id Value
1 F1-f2
2 F3-F4
3 F5-F6
4 F8-F9
5 F3-F2
6 F8-F7
Kindly advise ,
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Use Jeff Moden's string splitter function. See link below (the function can be found in a zip file at the end of the article)

Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

Example:

select ItemNumber AS ID, Item As Value

from dbo.DelimitedSplit8K('F1-f2~F3-F4~F5-F6~F8-F9~F3-F2~F8-F7', '~')

select b.ItemNumber AS ID, b.Item As Value

from (select 'F1-f2~F3-F4~F5-F6~F8-F9~F3-F2~F8-F7' as myString ) as a

cross apply dbo.DelimitedSplit8K(myString, '~') as b

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Abu Dina (11/15/2013)
Use Jeff Moden's string splitter function. See link below (the function can be found in a zip file at the end of the article)
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
Example:
select b.ItemNumber AS ID, b.Item As Value
from (select 'F1-f2~F3-F4~F5-F6~F8-F9~F3-F2~F8-F7' as myString ) as a
cross apply dbo.DelimitedSplit8K(myString, '~') as b

Let me second Abu's post on the approach. If you've never used this function before, be warned that the performance will make you wonder if it even did anything, but you get the right result. You'll never look at splits the same way again.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions