February 23, 2006 at 3:58 am
My source is a flat file, the target is a SQL server database.
If the file has a value as 12e4 which has to be inserted into a numeric field in the database. DTS inserts 12e4 as 120000. The requirement is that if any character is non numeric, I should raise an error. How can I acheive this?
thanks!
February 23, 2006 at 6:18 am
since this is a valid scientific notation number as well as a valid hex number. you will have to write your own function to test for non numeric values. IsNumeric will not cut it in this case. look to using a regular expression match. this will surely slow down your import, but you will get clean data... and that is usually most important.
-Mike Gercevich
February 23, 2006 at 11:29 pm
Thanks Mike,
Does that mean I can not "turn off" scientific notation at the server level? And please let me know what exactly you mean by "regular expression match". The input could be any combination of digits 0 to 9 an the numbers can be 1 to 9 digits wide. The input could be 976234 and also 425, ...etc
How will match with regular expressions unless I read each digit independently and compare it with digits 0 to 9?
Thanks,
Shailendra
February 25, 2006 at 6:05 pm
With the IsNumeric function you do not have any control over what will be returned. It often returns way too may 'true' results than you would wish for.
One thing you could do is use a Regular Expression (RegEx) and build a pattern match string such as "([^.0-9])?*" if any match is returned and the length of the input string is (Len(Trim(s)) > 0) = true, then you do not have an integer/decimal number. Remove the period and use a match patten of "([^0-9])?*" to find only integer values.
Check out this site if you are wanting to learn more about the power of regular expressions: http://regexlib.com
-Mike Gercevich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply