Need T-SQL to Parse string

Question

Post reply

Need T-SQL to Parse string

Express12

SSCoach

Points: 18514
More actions
April 20, 2005 at 11:02 am

#66193

Hi -- I have a 10,000 row table containing a column: AddrInst nvarchar(32) that contains "7" flavors of strings:
A;3175327;
I;1474158;H;Addr:2362516;
I;1013114;P;Addr:2362337;
I;1794227;W;Addr:2362534;
I;1596531;P;
O;10271;
O;10058;Addr:2362549;
(*** the integer values may change, but the 7 formats are the only variations)
I'm looking for T-SQL to parse these to accomodate these rules:
1. Alter table ~ add 4 columns: (I can create this ALTER statement!)
* Type_1       char(1)
* Type_1_ID int
* Type_2       char(1)
* Type_2_ID int
2. Inspect each string; parse as:
* Update Type_1      w/ 1st Byte in string (A,I,or O)
* Update Type_1_ID w/ numeric value following the 1st semi-colon
* Update Type_2      w/ 1st byte following 2nd semicolon ONLY IF the value = H, W, or P
* Update Type_2_ID w/ numeric value following the string ADDR:
BT

Viewing 6 posts - 1 through 5 (of 5 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

this can get you started

Update Tbl

set

Type_1 = Left(AddrInstr,1)

, Type_1_ID = cast(substring(AddrInstr,2,3-charindex(';',AddrInstr,3)) as int)

, Type_2 = case when AddrInstr Like ';P;' Then 'P'

when AddrInstr Like ';H;' Then 'H'

when AddrInstr Like ';W;' Then 'W'

else '' end -- what if not?

, Type_2_ID = case when charindex('Addr:') > 0 then

cast (substring(AddrInstr, Charindex('Addr:',AddrInstr,1) + 6, Charindex(':', 2, reverse(AddrInstr)) -1) as int)

else 0 end -- what if not?

* Noel

Express12 SSCoach Points: 18514 More actions · Answer 2

I tried your recommendation and it failed w/ the following error message:

The charindex function requires 2 to 3 arguments.

update STAGEtblCSComponentParsed

set Type_1 = left(AddrInstr,1),

Type_1_ID = cast(substring(AddrInstr,2,3-charindex(';',AddrInstr,3)) as int),

Type_2 = case when AddrInstr Like ';P;' then 'P'

when AddrInstr Like ';H;' then 'H'

when AddrInstr Like ';W;' then 'W'

else ''

end, -- what if not?

Type_2_ID = case when charindex('Addr:') > 0

then cast (substring(AddrInstr, Charindex('Addr:',AddrInstr,1) + 6, Charindex(':', 2, reverse(AddrInstr)) -1) as int)

else 0

end -- what if not?

BT

ron k SSCertifiable Points: 6103 More actions · Answer 3

I'm pretty sure Noel ment to add the % signs in the like statements.

Ex: ...when AddrInstr Like '%;P;%' Then 'P'...

noeld SSC Guru Points: 96590 More actions · Answer 4

I posted something to get you started not the real deal!

As you can see ron_k already posted some of my errors. I didn't test the query but if you need the real deal here you go!

create table data (AddrInstr varchar(50))

insert into data (AddrInstr )

select 'A;3175327;'

union all select 'I;1474158;H;Addr:2362516;'

union all select 'I;1013114;P;Addr:2362337;'

union all select 'I;1794227;W;Addr:2362534;'

union all select 'I;1596531;P;'

union all select 'O;10271;'

union all select 'O;10058;Addr:2362549;'

select

Type_1 = Left(AddrInstr,1)

, Type_1_ID = cast(substring(AddrInstr,3,charindex(';',AddrInstr,3) - 3) as int)

, Type_2 = case when AddrInstr Like '%;P;%' Then 'P'

when AddrInstr Like '%;H;%' Then 'H'

when AddrInstr Like '%;W;%' Then 'W'

else '' end -- what if not?

, Type_2_ID = case when charindex('Addr:',AddrInstr,1) > 0 then

cast (substring(AddrInstr, Charindex('Addr:',AddrInstr,1) + 5, Charindex(':', reverse(AddrInstr),1) -2) as int)

else '0' end -- what if not?

from data

* Noel

Express12 SSCoach Points: 18514 More actions · Answer 5

Noel / Ron- perfect. Your solution just got me over an 8 hour hump!!! Can I offer you a Philly Cheese Steak???

thanks a million!

bt

BT