April 7, 2007 at 7:12 pm
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?
April 7, 2007 at 8:00 pm
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
Change is inevitable... Change for the better is not.
April 9, 2007 at 2:07 pm
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?
April 12, 2007 at 6:34 am
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.
April 18, 2007 at 1:46 pm
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.
April 19, 2007 at 7:46 am
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.
April 19, 2007 at 8:13 am
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
Change is inevitable... Change for the better is not.
April 19, 2007 at 8:42 am
Thanks, my brain is still asleep. Well if anyone needs TSQL I will leave posted.
April 19, 2007 at 11:04 am
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy