ISNUMERIC Issue

Question

ISNUMERIC Issue

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Michael Valentine Jones (7/26/2010)
It would by handy if SQL Server had an enhanced IsNumeric function that you could pass a specific target data type to, but that's the way it is for now.

Heh... they already have it. Unfortunately for some, they called it "LIKE". 😛

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nevyn SSChampion Points: 14210 More actions · Answer 2

Eugene Elutin (7/26/2010)
Nevyn (7/26/2010)
The way "money" and thus "isnumeric" handles commas seems to be fairly dumb, though. Instead of validating commas in only certain placements, I guess they decided to just make commas transparent for the conversion.
For example, try select cast ('1,2.1,,,,3' as money)
I would not call it dumb. It is just flexible. SQL is "aware" that money are commonly formatted with commas, however no one guarantees that commas separate every three digits. You can format it as you wish (eg. #,##,####,#.##,#0). Therefore when converting such values into money it simply ingores commas...
check what it does with:
select CAST ('10,2,300.12,45' as money)

Right, now check what it does if you type only commas. Call it flexible if you wish, but it has no built in special case handling to keep from giving you something weird, which is then compounded when the same rules apply to isnumeric.