November 18, 2007 at 10:19 pm
Hi,
You can try the following query :
select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.
Hope that Helps 🙂
November 19, 2007 at 5:33 am
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
Change is inevitable... Change for the better is not.
November 19, 2007 at 5:35 am
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.
November 19, 2007 at 10:04 am
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
July 17, 2014 at 10:56 pm
thank you very much..
September 18, 2014 at 2:17 am
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
May 11, 2016 at 5:27 am
🙂 God bless you!
May 11, 2016 at 6:03 am
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 hasStarRating:
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.
May 11, 2016 at 7:01 am
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)
-- Itzik Ben-Gan 2001
July 10, 2018 at 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]
July 10, 2018 at 5:06 am
dva2007 - Tuesday, July 10, 2018 5:03 AMThis 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
July 10, 2018 at 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.
July 10, 2018 at 5:36 am
dva2007 - Tuesday, July 10, 2018 5:24 AMI 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
😎
July 10, 2018 at 6:39 am
dva2007 - Tuesday, July 10, 2018 5:24 AMI 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
Change is inevitable... Change for the better is not.
July 10, 2018 at 6:55 am
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