IsNumeric not working

Question

Post reply

IsNumeric not working

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
January 7, 2010 at 10:11 am

#137944

I'm trying to validate a text field that may contain the CustomerNo in it to be all numeric but the "IsNumeric" function is not doing it b/c the code below should return NULL if it's not all numeric. Below is piece I'm running, the results are below that.
Selectx.TaskId, x.TaskTypeId, (Case When IsNumeric(ltrim(rtrim(x.[Value]))) = 1 Then x.[Value] Else Null End) as CustNo
FromLifeExtension_PROD.dbo.tskTaskDetail(nolock) x
Inner Join
LifeExtension_PROD.dbo.tskTaskTemplate (nolock) z
Onx.TaskTypeId = z.TaskTypeId
Andx.[Index] = z.[Index]
Wherez.LabelText = 'Customer Number'
CostumerNumber
NULL
NULL
+2348097738930
-
,
,
-

Viewing 15 posts - 1 through 15 (of 16 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It's hard to tell without sample data.

Please provide table def, sample data to play with and expected results as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mike01 SSChampion Points: 11395 More actions · Answer 2

the value with the plus sign is actually a numeric value. It's the same as -6, but we usually don't put the + in front of positive numbers. I think that after you perform the IsNumeric check, then you should convert the value to numeric. So you could do case when IsNumeric(value) = 1 then cast(value as numeric) else null end. The plus sign will disappear, but the value is still numeric

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/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Please understand, ISNUMERIC is not the same as IS A NUMBER. The following, select isnumeric('1e1'), returns 1 as the value can be converted to a valid numeric value.

If you do a search on SSC you will find numerous discussions on this very topic.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Try a few of these to find the limits of IsNumeric:

select IsNumeric('1d10');

select IsNumeric('1e10');

select IsNumeric('0xF');

select IsNumeric('10');

select IsNumeric('Hello');

The only ones that are not "numeric" are the hex number and "Hello". The first two don't look numeric, but they are valid decimal notations.

If you just want strings that have digits in them only, then try using

Like '%[^1-9]%'

You can add punctuation to that if you want to accept numbers with negative signs, periods, commas, etc. But keep in mind that "1-10" (indicating "1 through 10") will come up as a "number" with this test if you allow hyphens as a negative sign.

Combining the two gives the most complete test.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

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

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

GSquared (1/7/2010)
If you just want strings that have digits in them only, then try using
Like '%[^1-9]%'

I think that should be NOT LIKE.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I think it may work better with a 0 in the range! >>>

SELECT 1 WHERE '111' NOT LIKE '%[^0-9]%'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

January 7, 2010 at 12:08 pm

#1100432

Missed that, good catch.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

Sorry, but that won't work either - you need the following:

SELECT 1 WHERE '111' NOT LIKE '%[0-9]%'

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Jeffrey Williams-493691 (1/7/2010)
Sorry, but that won't work either - you need the following:
SELECT 1 WHERE '111' NOT LIKE '%[0-9]%'

Not quite true if you're looking for values containing only digits. In that case, you need it to be...

SELECT 1 WHERE '111' NOT LIKE '%[^0-9]%'

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Jeffrey Williams-493691 (1/7/2010)
Sorry, but that won't work either - you need the following:
SELECT 1 WHERE '111' NOT LIKE '%[0-9]%'

Really? Try this please after removing the space from between the @ sign and the T in the variable name:

declare @ TestD varchar(12);

set @T estD = '1e1'

select 1 where @ TestD not like ('%[0-9]%')

select 1 where @ TestD not like ('%[^0-9]%')

set @ TestD = '111'

select 1 where @ TestD not like ('%[0-9]%')

select 1 where @ TestD not like ('%[^0-9]%')

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

Okay - I see what I was missing the first time. Thanks.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Heh... in this case, two NOTs make a right... or maybe a pair of properly ties walking shoes. 😀

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Jeffrey Williams-493691 (1/7/2010)
Okay - I see what I was missing the first time. Thanks.

Don't worry Jeff, that concept took about 2 hours to sink in my thick skull. I was also learning patindex at the time with all the patterns and that one is not that obvious!! even if it makes total sens once you figure it out.

I gues that's how Einstein felt :hehe:.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 14

another nifty thing we've been provided with :

declare @TestD varchar(12);

set @TestD = '1e1'

declare @testINT integer

begin try

set @testINT = convert(float,@TestD)

Print '[' + @TestD + '] is a number.'

end try

begin catch

Print '[' + @TestD + '] is not a number.'

end catch

begin try

set @testINT = convert(int,@TestD)

Print '*[' + @TestD + '] is a number.'

end try

begin catch

Print '*[' + @TestD + '] is not a number.'

end catch

/*

set @testINT = convert(int,@TestD)

Msg 245, Level 16, State 1, Line 34

Conversion failed when converting the varchar value '1e1' to data type int.

*/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me