Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Parse numbers

Posted on 2011-02-14
Medium Priority
343 Views
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)
``````
0
Question by:BrookK
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Expert Comment

ID: 34888824
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
``````
0

LVL 7

Accepted Solution

waltersnowslinarnold earned 1500 total points
ID: 34888854
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)
``````
0

Author Closing Comment

ID: 35232796
answer was close to what I was looking for
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
###### Suggested Courses
Course of the Month7 days, 1 hour left to enroll