Use IsNumeric() to evalute Ssring that starts with 0D or 0E

Question

Post reply

Use IsNumeric() to evalute Ssring that starts with 0D or 0E

vincent_low

SSC Veteran

Points: 266
More actions
May 26, 2004 at 1:48 am

#84581

Declare @s-2 varchar(9)
SET @s-2 = '0D1234'
IF isnumeric(@s)= 1
PRINT 'All Number'
ELSE
PRINT 'NOT All Number'
When @s-2 = 'ABCDE' , it will print out as 'NOT All Number'
When @s-2 = '0D123' , it will print out as 'All Number'
When @s-2 = '0E123' , it will print out as 'All Number'
Any idea how to solve this problem ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

David.Poole SSC Guru Points: 75905 More actions · Answer 1

IF isnumeric(@s)= 1 AND PATINDEX('%[A-Z]%',@s)=0

PRINT 'All Number'

ELSE

PRINT 'NOT All Number'

This is a weird one. At first I thought it was interpretting your value as a hexadecimal number but SET @s-2 = '0C1234' also prints Not all number as well.

LinkedIn Profile

vincent_low SSC Veteran Points: 266 More actions · Answer 2

vincent_low

SSC Veteran

Points: 266

May 26, 2004 at 7:29 pm

#507943

It works.Thanks

Vladan SSC-Insane Points: 22072 More actions · Answer 3

That's really strange... it only returns 1, if there is just one occurence of the character D or E, and it starts to evaluate as Not numeric if more than 3 digits follow the character. It looks like it could be because it understands the string as exponential form of a number. But why with 'D'???

Try this:

Declare @s-2 varchar(9)

SET @s-2 = '1D121'

IF isnumeric(@s)= 1

PRINT 'All numbers'

ELSE

PRINT 'NOT all numbers'

PRINT CAST(@s AS float)

Result :

All numbers

1e+121

This seems to confirm that I was on the right track:

Declare @s-2 varchar(9)

SET @s-2 = '1D1211'

IF isnumeric(@s)= 1

PRINT 'All numbers'

ELSE

PRINT 'NOT All numbers'

PRINT CAST(@s AS float)

NOT All numbers

Server: Msg 8115, Level 16, State 2, Line 7

Arithmetic overflow error converting expression to data type float.

vadba SSChampion Points: 11133 More actions · Answer 4

What's happening is that if the string contains one D or E, then SQL Server treats the string as scientific notation. If you cast as real, you'll see the range of numbers that cause an error will change

float: Floating precision number data from -1.79E + 308 through 1.79E + 308.

real: Floating precision number data from -3.40E + 38 through 3.40E + 38.

Declare @s-2 varchar(9)

---> 1111 * 10^305 = 1.111 * 10^308

SET @s-2 = '1111E305'

PRINT CAST(@s AS float)

PRINT CAST(@s AS real)

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

Declare @s-2 varchar(9)

-- OK ---> 1111 * 10^305 = 1.111 * 10^308 308 is max. exponent

SET @s-2 = '1111E305'

-- Next causes an Error

-- 1111 * 10^306 = 1.111 * 10^309 Exponent 309 is out of range

SET @s-2 = '1111E306'

IF isnumeric(@s)= 1

PRINT 'All numbers'

ELSE

PRINT 'NOT all numbers'

PRINT CAST(@s AS float)

Antares686 SSC Guru Points: 125444 More actions · Answer 5

That would then mean thet ISNUMERIC actually does and explicit conversion of whatever you are entering.

Vladan SSC-Insane Points: 22072 More actions · Answer 6

That's how I understood it, Antares... in fact that means that the name of the function is a bit misleading. It is easy to understand that as "contains only numbers", while the true meaning is "can be converted to number".

BOL : "ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types."

That covers it all right, but... It is a pity that none of the examples show the feature discussed in this thread; IMHO it isn't something that would be clear to everyone who reads the above description.

mkeast, that's what I was trying to explain, I'm just a bit confused why it works this way with "D". Probably it's just my ignorance, but I didn't know that D is also used in scientific notation.

no name Old Hand Points: 363 More actions · Answer 7

WHY it is behaving like that?

Yes that may be true. It is considering E, D letters as scintific notations for floating point.

Isnumeric checks weather the string would be casted to any of the data types not for all data types(int, float, mony, decimal)

see the examples

select convert(float, '1d44')

select convert(int, '1d44')

select convert(float, '1e44')

select convert(int, '1e44')

converting to int results in error

select convert(float, '1f44')

select convert(int, '1f44')

here bot results in error

If I am wrong please educate me.

Kalyan

vadba SSChampion Points: 11133 More actions · Answer 8

IsNumeric() checks to see if the string can be converted to one of the numeric data types, which includes int, float, money, and decimal.

BOL says:

"ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types."

IsNumeric() also returns 1 for integer values that are WAY out of range.

Try this:

Select IsNumeric('1234567890123451234567890123456768733333333333333333333333333333')

The statement "A return value of 1 guarantees that expression can be converted to one of these numeric types." is clearly not true, since range checking doesn't occur for integers and an overflow error will be raised.

Scientific notation is valid only for float and real data types. 'F' is not valid for scientific notation, only 'D' and 'E'

What Vincent appeared to need was an IsInteger() function, so I've include a stored procedure below, followed by some example usage, if anyone is interested.

Also, refer to David Poole's example using PatIndex().

Since SET commands cannot be used in a user defined function, the code was packaged as a stored procedure with an OUTPUT parameter.

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

DROP PROCEDURE uspStrToInt

GO

CREATE PROCEDURE uspStrToInt

(

@sNum varchar(20) = NULL,

@retVal int OUTPUT

)

AS

/*

int ranges from -2,147,483,648 to 2,147,483,647

*/

IF IsNumeric(@sNum)= 1 AND PatIndex('%[DE]%', @sNum) = 0

BEGIN

-- Valid integer (all digits), but now check for integer in SQL Server's range

-- Trap error and warning messages

-- If @snum cannot be converted, @retVal will be NULL

SET ANSI_WARNINGS OFF

SET ARITHABORT OFF

SET ARITHIGNORE ON

SET @retVal = CONVERT(int, @sNum)

-- Restore default settings for error handling

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET ARITHIGNORE OFF

END

ELSE

SET @retVal = NULL

GO

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

-- EXAMPLE USAGE

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

DECLARE @sNum varchar(20), @retVal int

SET @sNum = '2147483648'