"IsNumeric" test in an expression?

Question

Post reply

"IsNumeric" test in an expression?

Dream Weaver

Ten Centuries

Points: 1104
More actions
April 7, 2007 at 7:12 pm

#119542

In a derived column transformation I want to see whether a character column is numeric. So the expression would be something like:
isnumeric([column]? "dothis" : "dothat"
But of course there's no isNumeric function.
Is there a way to do this?

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

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

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

Actually, there is an ISNUMERIC function (I think... there was one in 2k... did they get rid of it?)

And, actually, you would never want to use it as an ISALLDIGITS function because, it isn't.

I recommend you read the following thread...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2

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

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 2

I perhaps wasn't clear enough in my original post. Yes, T-SQL has an IsNumeric function (although thanks for pointing out its sometimes-odd behavior), but that's not what I'm looking for.

Is there a way, in an SSIS expression, to determine whether a string contains all digits?

ivan jeremic Grasshopper Points: 17 More actions · Answer 3

Try to convert it to numeric type that data should be in. If it fails on red line do something if it doesn't on green line do something and then if you need all data together use Union All to connect the data back together.

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 4

Thanks for the advice. I actually am taking a different approach. I installed the Regular Expression Transformation from http://www.sqlis.com/91.aspx. This gives me a more general purpose tool. Sure, I can use it to find whether something is numeric but I can do a lot more with it too.

I use it similar to what Ivan described above. It takes one input and produces two outputs, one of which matches the regular expression pattern I specified, the other does not. Neither is an error, so both outputs have a green arrow.

Testing has gone very well and I just put this into production today, so far with success.

One note: the volume going through this data flow is not high. I have not tested the performance of this transformation. But as far as I can tell at low volumes it's very fast.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

No reason to use Regular Expressions. The only thing that will trip up IsNumeric with regards to preference is money and decimal all you do is something like this

declare @x varchar(40)

set @x = '$4,000.00'

If IsNumeric(Cast(@x as Varchar(40))) = 1 AND Cast(@x as Varchar(40)) NOT LIKE '%[$,.]%'

PRINT 'Yes'

ELSE

PRINT 'No'

As you see I added a check against $ , and . you can keep any of those you want but otherwise that ensures all are number values.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 6

Um.... they don't want it in T-SQL... they want an expression in SSIS and they said IsNumeric Doesn't work there...

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

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Thanks, my brain is still asleep. Well if anyone needs TSQL I will leave posted.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 8

Actually, there's a lot that will trip up ISNUMERIC... please see the following URL...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2

So... if you want an ISALLDIGITs and still allow conversion to the MONEY data type when money characters are present, then you have to do something like this (keeping the same tone as the previous code)...

DECLARE @x VARCHAR(40)

SET @x = '$4,000.00'

IF @x NOT LIKE '%[^0-9$,.]%'

PRINT 'Yes' -- Is all digits or is convertable as MONEY datatype

ELSE

PRINT 'No' -- Is not all digits or is not convertable as MONEY datatype

Of course, you can do a check a whole column returning only those rows that have the convertable data...

SELECT *

FROM sometable

WHERE somecolumnname NOT LIKE '%[^0-9$,.]%'

Note that the "^" is the symbol for "NOT"...

The only thing it doesn't do is check for the position and count of the non-numeric characters... for example, in U.S. currency, there should only be 1 decimal point.

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