Replacing comma with a tab - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Replacing comma with a tab

Author

Topic

offspring22
Starting Member

38 Posts

Posted - 2012-03-29 : 16:53:29

Hello,

I'm exporting some data into a tab delimited file with column headers. Some of the columns have data that is comma seperate, for example, the "parking" column can have data like "PAD,2GATT,ONSTR,RV" etc.

In that instance I need it to be each column, but also have a column header/name for each. So for the example above, my file would need to include:

parking parking parking parking
PAD 2GATT ONSTR RV

I couldn't tab in here so used spaces instead - but you get the idea.

Any suggestions?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-03-29 : 17:10:16

Select 'aaa,bb,ccc,ddddd,e,ff,g' as CSV, replace('aaa,bb,ccc,ddddd,e,ff,g',',',char(9)) as TSV

Select 'PAD,2GATT,ONSTR,RV' as CSV, replace('PAD,2GATT,ONSTR,RV',',',char(9)) as TSV

Srinika

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-29 : 17:13:56

replace(somecolumn, ',', char(9))

offspring22
Starting Member

38 Posts

Posted - 2012-03-29 : 17:22:51

I was able to get that on my own, but I need each to be in it's own column, with it's own column name when I include the headers.
so like in my example, I need it to be:

parking parking parking parking
PAD 2GATT ONSTR RV

Not:

parking
PAD 2GATT ONSTR RV

I might be able to get away with parking1, parking2 etc....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-29 : 19:49:18

It's a bit of a kludge (a bit?) but if there are always four elements in your string and there's no periods in your string:

[CODE];with Replaced
as (
select replace(MyColumn, ',', '.') MyColumn
from MyTable
)
select
ParseName(MyColumn, 4) Parking1,
ParseName(MyColumn, 3) Parking2,
ParseName(MyColumn, 2) Parking3,
ParseName(MyColumn, 1) Parking4
from Replaced[/CODE](Seriously, a bit of a kludge?)

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources