TSQL Parsing 3rd Element

Question

I have a field that contains data like this:

1763_250116_0_040
1763_250132_4_030
1763_250132_99999_030

I need to get out:

0
4
99999

How could I accomplish this with TSQL?

There are hundreds of questions about string splitting already. Please do some research first... The linked answer contains great approaches, but many of them are outdated. I'd have a look at the younger / more active ones... — Shnugo, Jun 29 '18 at 09:34

score 0 · Answer 1 · answered Jun 28 '18 at 20:03

Substring and some others string manipulations

declare @table table (c1 varchar(64))
insert into @table
values
('1763_250116_0_040'),
('1763_250132_4_030'),
('1763_250132_99999_030_000_0000_000')

select
    substring(right(c1, len(c1) - charindex('_',c1,charindex('_',c1,0) + 1)),0,charindex('_',right(c1, len(c1) - charindex('_',c1,charindex('_',c1,0) + 1))))
from @table

score 0 · Answer 2 · answered Jun 28 '18 at 20:56

If you have only 4 or less parts of numbers in the text and you always want the third part, then you should probably use the built-in function PARSENAME.

select PARSENAME (REPLACE(CONVERT(NVARCHAR(100),c1),'_','.') , 2)   
FROM T
GO

this solution best fit your sample data, but read the limitation which I mentioned above. For text in this format this should be best probably: <number>_<number>_<number which we want to get>_<number>

TSQL Parsing 3rd Element

2 Answers2