# Urgent! - How to extract number from a string

• Hi,

You can try the following query :

select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

Hope that Helps 🙂

• Hi,

You can try the following query :

select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

Avaneesh,

Try this and see why you shouldn't use ISNUMERIC as an "IsAllDigits" function...

SELECT ISNUMERIC('1e3')

SELECT ISNUMERIC('1d3')

SELECT ISNUMERIC('\$1,000.00')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• There is a problem with using ISNumeric to decipher a string. Certain character groups return TRUE such as 10E. So if in this case someone lives at apartment 10E you would get a positive result for 10E when only 10 waas wanted or expected.

I think the solutions proposed by Matt & Jeff are as close as you can get with this type of mess. Software can only do so much to clean up unknown data, and no matter how you decide to squeeze the results you'll always leave something out.

• How about this. Might be a bit long winded though. This will pull out 742.0 of the following strings

'On 742.0 Evergreen Terrace'

'The number is at the end 742.0'

' 742.0 Evergreen Terrace 1234'

It only picks up the first full number it comes across and then exits the loops. Though if your using null values you may have to play with it.

declare @STR varchar(100)

declare @Tmp varchar(1)

declare @numstr varchar(100)

declare @count int

declare @fndnum binary

select @STR=' 742.0 Evergreen Terrace'

select @count=1,@fndnum=0,@numstr=''

while @fndnum=0

begin

select @Tmp=right(left(@str,@count),1)

print @Tmp

select @count=@count+1

if isnumeric(@tmp)=1

while isnumeric(@tmp)=1

begin

select @numstr=@numstr+@tmp

select @Tmp=right(left(@str,@count),1)

print @numstr

select @count=@count+1

select @fndnum=1

end

if @count>len(@str) set @fndnum=1

end

select 'I have found the number of ' + @numstr

Nuke the site from orbit, its the only way to be sure... :w00t:

• thank you very much..

• I came across the same situation and I found the working solution for it.

Here is the link:

http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

• 🙂 God bless you!

• KMM-489657 (11/15/2007)

I was just reading thru this thread and I have a similar issue. I'm trying to extract the number string from a StarRating of Hotels column. For example the column currently has

StarRating:

0

1

1.5

NA

2

2 STAR

3.00

4-STAR

4

NULL

What I would like to see as the results in this column is:

0

1

1.5

0

2

2

0

3.00

4

4

0

And if possible replace the blank,NA and NULL with 0.

Could someone please assist.

`ISNULL(NULLIF(LEFT(StarRating,PATINDEX('%[^0-9.]%',StarRating+' ')-1),''),'0')`

Far away is close at hand in the images of elsewhere.
Anon.

• initials_mi (9/18/2014)

I came across the same situation and I found the working solution for it.

Here is the link:

http://www.ittutorials.in/source/sql/sql-function-to-extract-only-numbers-from-string.aspx

A set-based solution that does not use a loop is the faster way to go. In addition to what Jeff posted you could use the function discussed here: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx

or PatExclude8K (referenced in my signature)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• This may be useful.

DECLARE @strNumbers VARCHAR(100) = 'Final Figure 2008 and Qtr 2'

DECLARE @Pos SMALLINT = 0

SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character

WHILE (@Pos > 0)

BEGIN

-- Replace alphabet with empty string.

SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')

-- Find next alphabet

SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)

END

SELECT @strNumbers [Output]

• dva2007 - Tuesday, July 10, 2018 5:03 AM

This may be useful.

DECLARE @strNumbers VARCHAR(100) = 'Final Figure 2008 and Qtr 2'

DECLARE @Pos SMALLINT = 0

SET @Pos = PATINDEX('%[^0-9]%', @strNumbers) --Find first character

WHILE (@Pos > 0)

BEGIN

-- Replace alphabet with empty string.

SET @strNumbers = STUFF(@strNumbers, @Pos, 1, '')

-- Find next alphabet

SET @Pos = PATINDEX('%[^0-9]%', @strNumbers)

END

SELECT @strNumbers [Output]

A WHILE loop is a terrible way to do this, even for a problem that is 11 years old. 🙂

There was at least 1 set based solution that have been posted which would be far better.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

• I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

• dva2007 - Tuesday, July 10, 2018 5:24 AM

I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

You should have a look at these functions
😎

• dva2007 - Tuesday, July 10, 2018 5:24 AM

I already used the function given by Jeff dbo.fnLeaveOnly. Both of these gives me expected result however I found the solution I posted is quicker. We already had this posted function in data warehouse.

I suppose it depends on what kind of dataset we are using. The dataset I am using has only 200 rows and function fnLeaveOnly took 3 seconds to return data and the one I posted took less than a second.

Please post the function that you settled on.  Thanks.

And, agreed on the dbo.fnLeaveOnly... it's old stuff (scalar instead of iTVF) that I've replaced many years ago.
.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• After reading the article above I used dbo.DigitsOnlyEE which is very quick.

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply