Retrieve portion id from the specific characters defined

Transact-SQL
1

Hi All,

I want to retrieve the ID only appear inside the (invDetID:), how can i do it?

declare @text nvarchar(max)
set @text = 'DN00000043-526(DNItemID:947)-(invDetID:1995)'
--set @text = 'DN00000043-526(DNItemID:947)-(invDetID:15)'
--output =1995
--output=15

select SUBSTRING(@text,CHARINDEX('-(',@text)+1,(CHARINDEX(')-',@text)-CHARINDEX('(',@text))-1) <<Not working well

Please advise.

Thank you.

Regards,
Micheale

2

I got it.

Answer:

declare @text nvarchar(max)
set @text = 'DN00000043-526(DNItemID:9)-(invDetID:10001)'
Select Replace(SUBSTRING(@text,patindex('%invDetID:%',@text),(len(@text))-patindex('%invDetID:%',@text)),'invDetID:','')

Thank you.

Regards,
Micheale

3 
declare @text nvarchar(max)
set @text = 'DN00000043-526(DNItemID:9)-(invDetID:10001c)abcdef'
Select SUBSTRING(@text,charindex('invDetID:',@text)+9,
    charindex(')',@text,charindex('invDetID:',@text))-
    charindex('invDetID:',@text)-9)
1 Like