Isnumeric function - ISNUMERIC('1E2')

Question

Post reply

Isnumeric function - ISNUMERIC('1E2')

seaport

SSCrazy

Points: 2491
More actions
October 27, 2014 at 10:11 am

#300509

I have a sql statement converting varchar to bigint. Below are what I got.
select ISNUMERIC('1E2')
returns
1
select CAST('1E2' as bigint)
returns
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to bigint.
So I have to use the following code to test whether a varchar can be converted to a bigint.
where ISNUMERIC(TextID)=1 and charindex('E',TextID)=0; -- the requirement is that "E" is not supposed to be in the TextID
if "1E2" was a valid ID value, then I would have to write a separate code to convert 1E2 to 100.
Is there a better way to handle numbers like '1E2'?

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 100262 More actions · Answer 1

To check for anything other than only digits (0 thru 9), use:

NOT LIKE '%[^0-9]%'

for example:

SELECT

TextID,

CASE WHEN TextID NOT LIKE '%[^0-9]%' THEN 'OK' ELSE 'Bad' END

FROM (

SELECT '1E2' AS TextID UNION ALL

SELECT '07809475902174790857' UNION ALL

SELECT '80780976897.6'

) AS test_data

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

Take a look at this article. It shows what's happening and how to get an all-digits validation.

http://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183686 More actions · Answer 3

This is a possible option based on Scott's post. You might need to use additional validations and things can get really complex really quickly.

SELECT

TextID,

CASE WHEN TextID NOT LIKE '%[^0-9]%' THEN CAST( TextID as bigint)

ELSE CAST( CAST( TextID as float) as bigint) END

FROM (

SELECT '1E2' AS TextID UNION ALL

SELECT '07809475902174790857' UNION ALL

SELECT '80780976897.6'

) AS test_data

The best option is to correct this from the source.

EDIT: Forgot to include the code. :hehe:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

seaport SSCrazy Points: 2491 More actions · Answer 4

Thanks for all the help.

It works.

TextID NOT LIKE '%[^0-9]%'

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Remember ISNUMERIC simply tells you that a value may be converted to a numeric value, it just doesn't tell you what numeric value. Using your example of 1E2:

select CAST('1E2' as bigint);

--Msg 8114, Level 16, State 5, Line 1

--Error converting data type varchar to bigint.

go

select CAST('1E2' as float);

--Returns: 100

go

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

seaport (10/27/2014)
Thanks for all the help.
It works.
TextID NOT LIKE '%[^0-9]%'

As long as you don't have any decimal points or negative numbers.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?