How to change it to comma from a Tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to change it to comma from a Tab

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-11 : 19:13:39

Hi Guys,
I have one field in a table where there are multiple values and they have been separated by tab.I have to flatten this table but if i am not mistaken then i think Tally Table approach doesn't work with Tabs. I think it works only with commas.There are so many fields so i can't change it manually.Any help would be appreciated.Thanks in advance.Here is the sample data enclosed
Display_Name_id
103986 103811 103811 103811
103986 506765 104357 104366
103986 103986 103986
103986 103811 103811 103811
103986 564937
104348

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-11 : 20:01:13

Here's the tally concept for a generic delimiter. I used the tab delimiter as my example...


Declare @delim char(1)
Set @delim = char(9) --tab character

Declare @myTable table (myData varchar(100))
Insert Into @myTable
Select '103986'+@delim+'103811'+@delim+'103811'+@delim+'103811'
Union All Select '103986'+@delim+'506765'+@delim+'104357'+@delim+'104366'
Union All Select '103986'+@delim+'103986'+@delim+'103986'
Union All Select '103986'+@delim+'564937'
Union All Select '104348'
Union All Select '103986'+@delim+'103811'+@delim+'103811'+@delim+'103811'


Declare @numbers table (n int)
Insert Into @numbers
Select n = i1.n+i2.n+i3.n+i4.n+i5.n+i6.n+i7.n
From
	(Select n=0 Union Select 1) i1,
	(Select n=0 Union Select 2) i2,
	(Select n=0 Union Select 4) i3,
	(Select n=0 Union Select 8) i4,
	(Select n=0 Union Select 16) i5,
	(Select n=0 Union Select 32) i6,
	(Select n=0 Union Select 64) i7
Order By n

Select 
	A.myData, 
	B.n, 
	substring(@delim+A.myData+@delim,B.n+1,charindex(@delim,@delim+A.myData+@delim,B.n+1)-B.n-1)
From @myTable A, @numbers B
Where substring(@delim+A.myData+@delim,B.n,1)=@delim
and charindex(@delim,@delim+A.myData+@delim,B.n+1)>0

Corey

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-12 : 10:26:05

Thanks Corey!I appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources