isnumeric not working

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » isnumeric not working

isnumeric not working

Rate Topic

Display Mode

Topic Options

Author

Message

cRuchika

Posted Monday, November 09, 2009 1:49 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 179, Visits: 467

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

Post #816101

GSquared

Posted Monday, November 09, 2009 1:56 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 8,665, Visits: 4,943

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

- GSquared

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #816106

Jeff Moden

Posted Monday, November 09, 2009 9:24 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 20,149, Visits: 13,685

Heh... I normally hate the use of semantics but I have to deploy them for this one. Please forgive me... Blush

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

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

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #816287

« Prev Topic | Next Topic »

Permissions