Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

isnumeric not working Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 1:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:45 AM
Points: 336, Visits: 867
isnumeric not working.........

select isnumeric('26d5')

it should result in = 0

but it is showing me 1...any help?
why is it not working??

Thanks



---------------------------------------------------

Thanks
Post #816101
Posted Monday, November 9, 2009 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
"d5" means "decimal 5", similar to scientific notation. It means "26 followed by 5 zeroes". That's a number.

It's one of the main weaknesses of IsNumeric.

Sometimes, you have to use something like this:
declare @Str varchar(100);

select @Str = '26d5';

select
case patindex('%[^0-9]%', @Str)
when 0 then cast(@Str as Int)
else null
end;



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #816106
Posted Monday, November 9, 2009 9:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
Heh... I normally hate the use of semantics but I have to deploy them for this one. Please forgive me...

Despite appearances, ISNUMERIC is functioning properly even when it allows something as odd as your '26d5' example ... It works precisely the way it was designed to work and for exactly the task it was designed to solve. It identifies things that could be changed to some type of numeric data type. It was never desired for nor intended to be used as an ISALLDIGITS function. ISNUMERIC will even take currency symbols, commas, decimal points, engineering and decimal notations (like Gus pointed out), tabs, certain spaces, carriage returns, line feeds, and a whole bunch of other things.

Gus shows one possible way to derive functionality similar to an ISALLDIGITS function. You can also use WHERE somecolumnname NOT LIKE '%[^0-9]%' to check a whole column. For more information on the nuances of ISNUMERIC, please see the following article... heh... I can personally vouch for the author.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1073&lngWId=5



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #816287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse