isnumeric return wrong value for CHAR(9)=tab - by sqlbotdotnet

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 299346 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/24/2007 1:53:19 AM
Access Restriction Public


isnumeric return wrong value for CHAR(9)=tab
Sign in to post a comment.
Posted by Robert Heinig II on 5/3/2013 at 12:40 AM
Well-known issue now solved by
Posted by Microsoft on 10/4/2007 at 12:15 AM
Thanks for you feedback.

In the current implementation, ISNUMERIC returns 1 if the string data can be converted to any one of int, numeric, money, and float. In this case, select convert(money, char(9)) does succesfully return 0.0000, therefore ISNUMERIC(CHAR(9)) returns 1. String to money conversion ignores all white spaces, including tab. Similarly, select convert(money, '1,111') successfully returns 1111.0000, therefore ISNUMERIC('1,111') returns 1.

I'd agree that such behavior is not best in many scenarios. In general, people would want to know whether the string can be converted to a specific type, instead of one of many types. We'll consider adding such specific checks in the future.

Best regards,
Jun Fang
Posted by Mighty-O on 9/24/2007 at 10:39 AM
Isnumeric - Comma validates but can’t cast as int or numeric.

Isnumeric results in false positives when testing a string value that contains a comma, e.g. 1,111, tests positive:
SELECT isn = ISNUMERIC( '1,111' )
Result: isn = 1

If one attempts to cast or convert the value:
SELECT i = cast( '1,111' as numeric(10,0) )

One is greeted with this message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Workaround is to replace the commas with a zero length space:
select i = cast( replace( '1,111', ',', space(0)) as numeric(10,0) )
Result: i = 1111