Separate one column to multiple columns

Question

Separate one column to multiple columns

peter_ping

SSC Rookie

Points: 31
More actions
September 16, 2002 at 8:24 am

#105791

There is a column like this: John Smith,Dept of HR,ABC Company,123 Main St,New York,NY 10021. How do I to separate this one column to multiple columns by comma delimiter as following format: John Smith|Dept of HR|ABC Company|123 Main St|New York|NY 10021.
Thanks. - Peter

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

I think you will need to write a script that will search through the string until it finds a comma and then write that portion of text to a variable. This will need to loop through until you hit the end of the string, you should have a full set of variables (for name, department, company etc etc) which you can pass into an UPDATE or INSERT statement.

You will need to hold pointers to the positition of the last comma, the current string position. Also if you have comma's within the string itself (other than the delimeters) then you'll have all kinds of fun!

Out of curiosity. Is this string imported into SQL Server that way? Could you run the seperation script on the import rather than running a script once the data is in the table?

My personal preference would be to have the columns split when the data is imported or failing that, import the strings into a import table, run the seperation script and then move the seperated data to the production table(s).

Clive Strong

clivestrong@btinternet.com

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Here is a very ugly TSQL SELECT command, that only works on your example. This example breaks apart you original string based on commas using CHARINDEX, SUBSTRING and LEN functions.

declare @s-2 varchar(200)

set @s-2 = 'John Smith,Dept of HR,ABC Company,123 Main St,New York,NY 10021.'

select substring(@s,1,charindex(',',@s)-1),

substring(substring(@s,charindex(',',@s)+1,len(@s)),1,charindex(',',@s)-1),

substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),1,charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))-1),

substring(substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)),1,charindex(',',substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)))-1),

substring(substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)),charindex(',',substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)))+1,len(@s))

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

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

peter_ping SSC Rookie Points: 31 More actions · Answer 3

Thanks very much. It works fine for my less than 1000 rows, table. - Peter

quote:
Here is a very ugly TSQL SELECT command, that only works on your example. This example breaks apart you original string based on commas using CHARINDEX, SUBSTRING and LEN functions.
declare @s-2 varchar(200)
set @s-2 = 'John Smith,Dept of HR,ABC Company,123 Main St,New York,NY 10021.'
select substring(@s,1,charindex(',',@s)-1),
substring(substring(@s,charindex(',',@s)+1,len(@s)),1,charindex(',',@s)-1),
substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),1,charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))-1),
substring(substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)),1,charindex(',',substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)))-1),
substring(substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)),charindex(',',substring(substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)),charindex(',',substring(substring(@s,charindex(',',@s)+1,len(@s)),charindex(',',@s)+1,len(@s)))+1,len(@s)))+1,len(@s))
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples