is there a true "sNumeric() in Sql 2005?

[jsteph]

Hi all,
I've never noticed before, but sql 2005's IsNumeric() returns true for the \, $, . characters--and various other non-numeric (in my view) characters that happen to be used in numeric formatting.

So I'm wondering if there's a built-in sql server function that returns true if the value of a field is truly numeric. I have a statement that just started blowing up because it happened to get some fields with a lone $ sign or slash. I thought I was protected using the IsNumeric but no, and these lone characters just started popping up in the data.

I know I could build one, but one would think this would be a no-brainer for an internal function. I'm also surprised I've never noticed this behaviour before.

Anyway, does this exist, or is this something where people write their own?
Thanks,
--Jim

 

[bborissov]

DECLARE @Test varchar(200)
SET @Test = '$'
SELECT ISNUMERIC(@Test),
       ISNUMERIC(@Test+'.e2')
SET @Test = '1'
SELECT ISNUMERIC(@Test),
       ISNUMERIC(@Test+'.e2')

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ousoonerjoe]

The MONEY data type also supports the '$'.

DECLARE @m	MONEY;
SET @m = '$.11';
IF ISNUMERIC(@m) = 1
	SELECT @m, '@m is numeric.';
ELSE
	SELECT @m, '@m not a number.';

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[markros]

Take a look here

http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-aware

PluralSight Learning Library

 

[jsteph]

Thanks for these replys, but they essentially just reinforce what I'm saying.

What I'm wondering is: Is there a built in function that will tell me if a value is actually numeric?

A field containing the single character "$" or "." or "\" is very much *not* numeric, in my opinion, yet IsNumeric says it is.

I'm just looking for a built-in function that will be a little more accurate about what's truly "a number".
Thanks,
--Jim

 

[djj55]

When I tried the program from the link markros provided it seemed to do what you want.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[jsteph]

<<When I tried the program from the link markros provided it seemed to do what you want>>
Yes and no. First, it's not built in, but I can live with that.

The key thing that I want is to know if the value of a particular variable or field is an actual number in and of itself, and I don't want to have to specify data types because this particular application will not know (and should not need to know) what the data type will be--it just needs to know if it can do plain arithmetic on the values.

So the problem I'm having is that the character "$" or the letter "e" are not numbers. I don't see how anyone can think they are. I know that MS returns the True because the user-interface *formats* certain values using those characters, that they must be numbers even if they exist alone--but in my opinion that is flat out wrong.

So I'll build some kludge for this and get it done,
Thanks,
--Jim