Check If Column Value Is Numeric. Ssis

I have a column with datatype of varchar. I would like to replace all the values that are not numeric with NULL. So for example my column can contain a value of MIGB_MGW but also 1

Solution 1:

If you want a null of varchar type, you can use NULL(DT_STR). For a DT_I4 you can use NULL(DT_I4) etc.

You can then use (DT_I4)kbup in place of your 1 to return the original varchar value that you want to keep, converted to a DT_I4:

(DT_I4)kbup == (DT_I4)kbup ? (DT_I4)kbup : NULL(DT_I4)

Solution 2:

Use NOT LIKE

SELECTCASEWHEN col NOTLIKE'%[^0-9]%'THEN col
         ELSENULLENDas Only_Numeric
FROM   (VALUES ('MIGB_MGW'),
               ('1352')) tc(col) 

Result :

Only_Numeric
------------NULL1352

Solution 3:

You could just convert them with a Derived Column and then use the ignore failure option in the Error output.

Solution 4:

Another option if 2012+ is Try_Convert()

SELECT Try_Convert(float,col)
FROM   (VALUES ('MIGB_MGW'),
               ('2.6e7'),
               ('2.6BMW'),
               ('1352')) tc(col) 

Returns

NULL26000000NULL1352