How to handle ISNUMERIC() function ?
I have a task (Derived Column Task) and I want to write something like this : IsNumeric(aColumnOfString) == true ? "All numbers" : "there are some characters" Here aColumnOfString can be something like "123a5" or 12345". I do not want to simply check if the left-most character is a number or not. I want to check the entire expression and return me a TRUE or false. A TRUE is returned ifthe entireexpression contains ONLY numbers, and FALSE otherwise. I read some posting using regular expression. But that is not a solution for this situation. Anyone knows how to accomplish this, please help!
August 31st, 2006 11:31pm

Steve, To determine if astring contains only numbers, you could use the data conversion transformation to convert the string to numeric. the strings that convert okay are all numbers. set theerror to redirect rows.the error data stream would be the strings that contain characters. Frank
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2006 4:08am

Hi, try this... ********************************************************************************************************************* Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Try Row.COLUMN = CStr(CType(Row.COLUMN, Int64)) Catch Row.COLUMN = Row.COLUMN End Try End Sub End Class ********************************************************************************************************************* You write this code in a script component and select the COLUMN as an input column with readwrite enabled properties. I hope it helps. Regards, Oscar.
March 27th, 2008 7:08pm

Here are some workarounds for the missing IsNumeric expression: http://microsoft-ssis.blogspot.com/2011/04/isnumeric-or-isnumber-expression-is.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 6:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics