SQL Server Performance Forum – Threads Archive

ISNumeric()

My developers had a trouble using ISNumeric(). ISNumeric(‘$500’) returns TRUE while they want to return FALSE. BOL says ISNumeric()returns true for all kind of numerics and money. I can write a function to check if there are only numbers and period. Is there a better solution? CanadaDBA

A friend gave an interesting solution:

quote:Originally posted by robvolk SELECT CASE WHEN ‘$500’ LIKE ‘%[^0-9.]%’ THEN ‘Not Numeric’ ELSE ‘Is Numeric’ END

But I couldn’t undrestand how LIKE ‘%[^0-9.]%’ works. What’s the trick behind that?
CanadaDBA

It’s a fairly regular LIKE expression: … I’ll just skip the % wildcards… The square brackets [ and ] mean that we’re looking for a match in a range of characters. However, the ^ sign at the beginning of the range is the negative version, so we’ll get a match if there’s a character that is NOT in the given range. By the way, the period should NOT be supplied in the range of characters, because periods ARE allowed.
My bad.

As I have found it is working like "If there is a character that is not in the range of 0 to 9" OR "is period". Therefore, it returns TRUE for 500.00 but FALSE for $500 CanadaDBA

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FarhadR As I have found it is working like "If there is a character that is not in the range of 0 to 9" OR "is period". Therefore, it returns TRUE for 500.00 but FALSE for $500<hr height="1" noshade id="quote"></blockquote id="quote"> I think my correction to my previous post pointed out the same issue.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Yeh, I saw your correction after I sent you my post.
Thanks Adriaan, CanadaDBA

however it thinks that 500.0.0.0 is a valid number when it is not… and that 50e+2 is not a valid number when it is Cheers
Twan

Okay Twan, you win – DECLARE @Amnt VARCHAR(100) SET @Amnt = ‘500.0.0.0’ SELECT CASE WHEN (@Amnt LIKE ‘%[^0-9.]%’)
OR (LEN(@Amnt) – LEN(REPLACE(@Amnt, ‘.’, ”)) > 1)
OR @Amnt = ‘.’ OR @Amnt = ” OR @Amnt IS NULL
THEN ‘Not Numeric’ ELSE ‘Is Numeric’ END

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Twan however it thinks that 500.0.0.0 is a valid number when it is not… and that 50e+2 is not a valid number when it is Cheers Twan <hr height="1" noshade id="quote"></blockquote id="quote"> Twan, You obviously have a version that also covers pi and the square root of -1.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

I used the following code instead of using ISNumeric():
DECLARE @Str VarChar(50) SELECT CASE WHEN @Str LIKE ‘%[^0-9.]%’
THEN 0
ELSE IsNumeric(@Str)
END Thanks for postings, CanadaDBA

and infinity… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Twan

CanadaDBA, No need to do ISNUMERIC() after the LIKE – if the expression does not match the criteria, then all the characters in the expression are acceptable. If that is the case, then the result of ISNUMERIC(@Str) is always 1. You can actually also use the LIKE keyword when controlling the flow of a procedure, like this:
IF (@Str LIKE ‘%[^0-9.]%’)
BEGIN
— respond to incorrectly formatted parameter
END

The ELSE part takes care of ‘500.0.00’ cases and returns 0 which means it is not a number. For my case, some numbers come with $ sign and I want to control them.

quote:Originally posted by Adriaan …
No need to do ISNUMERIC() after the LIKE – if the expression does not match the criteria, then all the characters in the expression are acceptable. If that is the case, then the result of ISNUMERIC(@Str) is always 1.
…

CanadaDBA

should it also cater for negative numbers? maybe something like isnumeric( @str ) = 1
and @str like replicate( ‘[-+0-9.eE]’, datalength( @str ) ) Cheers
Twan

Good point! Also you are demonstarting another usage of LIKE. I mean you have removed % and used REPLICATE. Interesting!
I should check it tomorrow.

quote:Originally posted by Twan should it also cater for negative numbers? maybe something like isnumeric( @str ) = 1
and @str like replicate( ‘[-+0-9.eE]’, datalength( @str ) ) Cheers
Twan

CanadaDBA

Just wondering why datalength was used in the following:

quote:isnumeric( @str ) = 1
and @str like replicate( ‘[-+0-9.eE]’, datalength( @str ) )

I have been playing around with this and had to use len(@str)

]]>