CODEWITHSUNDEEP
sqlsap-ase
itro
itro

Reputation: 7228

how extract sub-string from string in sybase?

With a query i get this string "Order::Resource(PPP32#BB300320LQ00J#AAAR05504)". I want to extract string before, between and after # character as following:

id = PPP32

sub_id = BB300320LQ00J

sup_id =AAAR05504

Does anyone know how to do this?

Upvotes: 2

Views: 29638

Answers (2)

podiluska
podiluska

Reputation: 51494

declare @start int, @end int, @secondstring varchar(100)

select @start = charindex('#',@string)
select @secondstring = substring(@string, @start+1, len(@string))

select @end = charindex('#',@secondstring)

select substring(@string,1, @start-1),
       substring(@string,@start+1,@end-1), 
       substring(@string, @start+@end+1, len(@string)-@end)

Upvotes: 3

aF.
aF.

Reputation: 66697

Here's the correct solution:

declare @string varchar(50)
select @string = 'Order::Resource(PPP32#BB300320LQ00J#AAAR05504)'
declare @start int, @end int, @secondstring varchar(100)

select @start = charindex('#',@string)
select @secondstring = substring(@string, @start+1, len(@string))

select @end = charindex('#',@secondstring)

select substring(@string,charindex('(', @string)+1, @start-1-charindex('(', @string)),
       substring(@string,@start+1,@end-1), 
       substring(@string, @start+@end+1, len(@string)-(@start+@end+1))

Upvotes: 3

Related Questions