Urgent! - How to extract number from a string

Question

Post reply

Urgent! - How to extract number from a string

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

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

Avaneesh -388582 SSChasing Mays Points: 657 More actions · Answer 1

Hi,

You can try the following query :

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

Hope that Helps 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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)

Robert O'Byrne SSCommitted Points: 1967 More actions · Answer 3

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.

EvilPostIT SSCrazy Points: 2578 More actions · Answer 4

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:

phaniraj1987 SSC Journeyman Points: 86 More actions · Answer 5

phaniraj1987

SSC Journeyman

Points: 86

July 17, 2014 at 10:56 pm

#1730488

thank you very much..

initials_mi SSC Rookie Points: 25 More actions · Answer 6

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

onwukanjown Grasshopper Points: 23 More actions · Answer 7

onwukanjown

Grasshopper

Points: 23

May 11, 2016 at 5:27 am

#1878108

🙂 God bless you!

David Burrows SSC Guru Points: 65138 More actions · Answer 8

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.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 9

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

dva2007 SSCertifiable Points: 7707 More actions · Answer 10

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]

Thom A SSC Guru Points: 99126 More actions · Answer 11

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

dva2007 SSCertifiable Points: 7707 More actions · Answer 12

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.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 13

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
😎

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

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)

dva2007 SSCertifiable Points: 7707 More actions · Answer 15

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