Split a string into columns - 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

General SQL Server Forums

New to SQL Server Programming

Split a string into columns

Author

Topic

jacekw
Starting Member

3 Posts

Posted - 2010-12-02 : 11:13:37

Hi All,

I have a column named "description".
All rows in this column have a string
"Name: John Surname: White Age:28"
"Name: Mark Surname: Travolta Age:58"

I have also three other columns, where I would like to store the data as seperated fields. The names are: oName, oSurname, oAge.

Any tips on splitting it into:

description oName oSurname oAge
==================================================================
"Name: John Surname: White Age:28" | John | White | 28
"Name: Mark Surname: Travolta Age:58" | Mark | Travolta | 58

It would be much appreciated.
WJ

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 11:27:41

something like

select name = substring(col,nameloc+6,surnameloc-nameloc-7),
surname = substring(col,surnameloc+10,age-surnameloc-11),
age = substring(col,ageloc+5,endloc-ageloc-4)
from
(
select col, nameloc = charindex('Name:',col), surnameloc = charindex('Surname:',col), ageloc = charindex('Age:',col), endloc = len(col)
from tbl
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jacekw
Starting Member

3 Posts

Posted - 2010-12-02 : 16:58:40

Great! It works, I just added some little adjustments in offset: Here is the full code now:

DECLARE @Sample TABLE
(
Col VARCHAR(200)
)

INSERT @Sample
SELECT 'Name: John Surname: White Age:28' UNION ALL
SELECT 'Name: Mark Surname: Travolta Age:58'

select name = substring(Col,nameloc+6,surnameloc-nameloc-7),
surname = substring(Col,surnameloc+9,ageloc-surnameloc-9),
age = substring(Col,ageloc+4,endloc-ageloc-3)
from
(
select Col, nameloc = charindex('Name:',Col), surnameloc = charindex('Surname:',Col), ageloc = charindex('Age:',Col), endloc = len(Col)
from @Sample
) a

jacekw
Starting Member

3 Posts

Posted - 2010-12-02 : 17:15:36

I see that live gets more complicated and the data format is not very strict.

Sometimes the records don't contain Name, sometimes Surename is missing. The order also can be changed. Like in the table below:

DECLARE @Sample TABLE
(
Col VARCHAR(200)
)

INSERT @Sample
SELECT 'Name: John Surname: White Age:28' UNION ALL
SELECT 'Name: Mark Surname: Travolta Age:58'UNION ALL
SELECT 'Surname: NoName Age:99' UNION ALL
SELECT 'Name: FamilyNameHere Age:24' UNION ALL
SELECT 'Age:24 Name: Jack Surname: Mixture'

Any thips on that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources