September 14, 2012 at 2:27 am
I think this "could" be solved using a CLR function that tries to cast the value to each acceptable numeric data type, but I never did CLR.
Unfortunately you cannot do this in a T-SQL function due to the lack of error handling with Try/Catch.
You can do this in a stored procedure, so something similar to the following would *technically* work in a row by row (cursor) scenario for smaller tables, where performance doesn't matter:
CREATE PROC dbo.spuTestNumeric @Number VARCHAR(50), @IsNumeric INT = 0 OUTPUT
AS
DECLARE @Try INT = 0;
DECLARE @Cast SQL_VARIANT;
START:
BEGIN TRY
IF @Try<=0 BEGIN SET @Cast =CAST(@Number AS int); SET @IsNumeric=1; END
IF @Try<=1 BEGIN SET @Cast =CAST(@Number AS numeric); SET @IsNumeric=1; END
IF @Try<=2 BEGIN SET @Cast =CAST(@Number AS bigint); SET @IsNumeric=1; END
IF @Try<=3 BEGIN SET @Cast =CAST(@Number AS money); SET @IsNumeric=1; END
IF @Try<=4 BEGIN SET @Cast =CAST(@Number AS smallint); SET @IsNumeric=1; END
IF @Try<=5 BEGIN SET @Cast =CAST(@Number AS smallmoney); SET @IsNumeric=1; END
IF @Try<=6 BEGIN SET @Cast =CAST(@Number AS tinyint); SET @IsNumeric=1; END
IF @Try<=7 BEGIN SET @Cast =CAST(@Number AS float); SET @IsNumeric=1; END
IF @Try<=8 BEGIN SET @Cast =CAST(@Number AS decimal); SET @IsNumeric=1; END
IF @Try<=9 BEGIN SET @Cast =CAST(@Number AS real); SET @IsNumeric=1; END
END TRY
BEGIN CATCH
SET @Try+=1;
GOTO Start
END CATCH
RETURN;
The procedure would need to be adjusted to include only the datatypes that one considers as numeric.
Or there can be one for each data type or the datatypes could be passed as parameters. So this is just the concept itself.
Best Regards,
Chris Büttner
September 14, 2012 at 2:53 am
I tried '%[^0-9]%' and it does not seem to work.
create table #test (string char(10))
insert into #test values ('12334')
insert into #test values ('abc')
insert into #test values ('123.3')
insert into #test values ('-123.3')
insert into #test values ('123$')
select * from #test where string not like '%[^0-9]%'
Instead of returning 12334 it does not return anything. We are running SQL Server 2005 SP4.
September 14, 2012 at 3:13 am
pkosiavelos (9/14/2012)
I tried '%[^0-9]%' and it does not seem to work.create table #test (string char(10))
insert into #test values ('12334')
insert into #test values ('abc')
insert into #test values ('123.3')
insert into #test values ('-123.3')
insert into #test values ('123$')
select * from #test where string not like '%[^0-9]%'
Instead of returning 12334 it does not return anything. We are running SQL Server 2005 SP4.
The datatype char(10) is fixed length, so the string '12334' is padded with 5 spaces to a length of 10. And since those spaces are not numeric characters, the string is rejected by the LIKE filter.
Try using datatype varchar(10) instead. Or add an RTRIM function in the query ("... where RTRIM(string) NOT LIKE ...")
September 14, 2012 at 3:37 am
Hugo, many thanks... it works fine. 🙂
September 14, 2012 at 3:42 am
You can convert to data type money everything identified as isnumeric().
Try this:
[font="Courier New"]SELECT [Ascii Code] = STR(Number),
[Ascii Character] = CHAR(Number),
[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
into #temp
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 255
AND ISNUMERIC(CHAR(Number)) = 1
select *,
convert(money, [Ascii Character])
from #temp
where [ISNUMERIC Returns] = 1
[/font]
September 14, 2012 at 5:05 am
Thanx a lott.. A new information for me.. 🙂
September 14, 2012 at 8:02 am
Very nice article! There is a typo:
"164 (Yen sign)" should actually reference 165.
September 14, 2012 at 8:56 am
Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 14, 2012 at 9:11 am
ronmoses (9/14/2012)
Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?ron
The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?
>L<
September 14, 2012 at 9:11 am
WayneS (12/1/2010)
I have a question for clarification:Ascii Codes 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.
Ascii Code 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.
Is ASCII 164 the Yen (1st quote), or something else (2nd quote)? It does appear to be the Yen, and you didn't cover ASCII 165, so in the second quote should that be 165?
Amen to both of you. I'm not so interested in "what is all digits" as I am in "what will survive a cast/convert". I see that the IsReallyNumeric codeset on http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html, pointed to by various people here, does pretty much what I want.
I particularly appreciate that article coming at the problem from different architecture levels although my personal experience (even though I is an architect <g>) is that you'd better do it at the database level, even if your web tier standards assures you of appropriate data coming in. And this is not just belt-and-suspenders -- ask me to explain if interested!
But I also think the function to which yuri 38568 linked (http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/) is a pretty nice idea to resolve the original and general "is numeric" question - with my limited understanding - so does anybody else have any reason not to use this approach? Later on in the thread, clark_button appears to be doing something similar.
Thanks to everybody in this thread, and especially to Jeff as usual!.
>L<
September 14, 2012 at 9:18 am
Lisa Slater Nicholls (9/14/2012)
ronmoses (9/14/2012)
Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?
Yes, I would definitely only use something like this for small data sets. But I thought I'd toss it in there as an idea.;-)
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
September 14, 2012 at 12:23 pm
Lisa Slater Nicholls (9/14/2012)
I see that the IsReallyNumeric codeset on http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html, pointed to by various people here, does pretty much what I want.
Oh, be careful, Lisa. The "filter" built into the method of the article can discriminnate for or against just about anything you want, as well. The problem with the method used in the link you cited is that it uses a scalar function which has some pretty severe performance problems that I wouldn't even justify the use of for supposedly small data sets.
The method in the article can use an "iSF" or "Inline Scalar Function" (for lack of a better term). Please see the following information for more on that subject.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2012 at 12:26 pm
ronmoses (9/14/2012)
Lisa Slater Nicholls (9/14/2012)
ronmoses (9/14/2012)
Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?
Yes, I would definitely only use something like this for small data sets. But I thought I'd toss it in there as an idea.;-)
ron
Just as a side bar, Ron... I try to never incorporate something that will only be used for small data sets because, once it's out there, it's difficult to control how and on what something will be used for especially if someone is fighting a tight schedule. It usually takes no more effort to make something scalable.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2012 at 12:28 pm
s b dragoo (9/14/2012)
Great little article. Several years ago while I still was a junior DBA, I ran into this very issue. It was quite perplexing at the time (and unfortunately involved people's property tax bills). I finally figured out the error of my ways. Your article has pointed out a couple of characters that I was not aware of still. Greatly appreciated!
You bet. Thanks for stopping by, Scott.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2012 at 12:32 pm
josuecalvo (9/14/2012)
You can convert to data type money everything identified as isnumeric().Try this:
[font="Courier New"]SELECT [Ascii Code] = STR(Number),
[Ascii Character] = CHAR(Number),
[ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
into #temp
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 255
AND ISNUMERIC(CHAR(Number)) = 1
select *,
convert(money, [Ascii Character])
from #temp
where [ISNUMERIC Returns] = 1
[/font]
Understood but I'm not sure what your point is. The purpose of the article was to show that ISNUMERIC shouldn't be used to determine if a character string is all numeric digits. The MONEY conversion will all for commas, decimal points, dollar signss, and other things that aren't numeric digits.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 106 through 120 (of 168 total)
You must be logged in to reply to this topic. Login to reply