breaking string into multiple column

Question

breaking string into multiple column

ahmadjk

SSC Journeyman

Points: 82
More actions
January 22, 2010 at 8:01 am

#214788

Hi
I am trying to extract information from the following string, it appears with quotation marks in sql table field.
"sip:111163211161@192.168.19.80:1720;unix_sa=192.168.19.80;unix_realm=Realm-M10-egress;unix_irealm=Realm-M00-ingress2;unix_iwf_itrusted"
I need to break it down using (;) semicolon as delemiter
Result should be like this
Column1 = "sip:111163211161@192.168.19.80:1720
Column2 = unix_sa=192.168.19.80
Column3 = unix_realm=Realm-M10-egress
Column4 = unix_irealm=Realm-M00-ingress2
Column5 = unix_iwf_itrusted"
Thanks

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

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

José.Cruz Right there with Babe Points: 722 More actions · Answer 1

Hi there,

If the text you are plitting starts and ends with the ";" dellimiter you can use the Tally Table[/url] example to slip it:

DECLARE @Parameter VARCHAR(8000)

SET @Parameter = ';sip:111163211161@192.168.19.80:1720;unix_sa=192.168.19.80;unix_realm=Realm-M10-egress;unix_irealm=Realm-M00-ingress2;unix_iwf_itrusted;'

SELECT [1] as Column1, [2] as Column2, [3] as Column3, [4] as Column4, [5] as Column5

FROM

(

SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(';',@Parameter,N+1)-N-1) as splitt , row_number() over(order by N) as level

FROM (

select top 1000 row_number() over (order by id) N

from syscolumns

) Tally

WHERE N <LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = ';'

) AS Splitt

PIVOT (

max(splitt)

FOR level IN ([1],[2],[3],[4],[5])

) as pvt

José Cruz

ahmadjk SSC Journeyman Points: 82 More actions · Answer 2

Thanks for your reply

I am running sql server 2000.

while I try to run the script I got an error message

Server: Msg 195, Level 15, State 10, Line 7

'row_number' is not a recognized function name.

Server: Msg 195, Level 15, State 1, Line 9

'row_number' is not a recognized function name.

GSquared SSC Guru Points: 260824 More actions · Answer 3

There's a great article on how to do that kind of thing at http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon