Parse numbers

Hello All,

I have to write a function to parse numbers after dash ('-') but excluding the zeros from the strings below-
'NA3000.~1A-003'
'NA457~1A.02-5'
'NA200.~1AS'
'NP42201~1.02-02'

Result-
3
5
0 -if no dash
2

This is what I have done so far-
Can anyone help me?
declare @delimiter varchar(10), @string varchar(100), @string1 varchar(100)
select @delimiter = '-', @string = 'KP008~1.003-002'

select @string1 = ISNULL(SUBSTRING(@string, CHARINDEX(@Delimiter, @string)+1, LEN(@string)), 0)

Open in new window

BrookKAsked:
Who is Participating?
 
waltersnowslinarnoldConnect With a Mentor Commented:
Use the following sample. it will help.
declare @delimiter varchar(10), @string varchar(100), @string1 varchar(100)
select @delimiter = '-', @string = 'KP008~1.003-005'
IF (PATINDEX('%-%',@string) > 0)
select @string1 = REPLACE(ISNULL(SUBSTRING(@string, CHARINDEX(@Delimiter, @string)+1, LEN(@string)), 0),0,'')
ELSE
select ISNULL(@string1,0)

Open in new window

0
 
wdosanjosCommented:
I think you just need to add a CAST and you should be good:

declare @delimiter varchar(10), @string varchar(100), @string1 varchar(100)
select @delimiter = '-', @string = 'KP008~1.003-002'

select @string1 = CAST(ISNULL(SUBSTRING(@string, CHARINDEX(@Delimiter, @string)+1, LEN(@string)), 0) as int)
select @string1

Open in new window

0
 
BrookKAuthor Commented:
answer was close to what I was looking for
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.