Table of contents

IsNumeric() funciton is too forgiven

One of my SSIS packages failed recently with the following error message.

“…. failed with the following error: "Error converting data type varchar to bigint….”

It turned out that a field contained some white spaces (such as tab, line feed and carriage return control characters), and the cast (col as bigint) has failed.

I can guarantee that all ETL developers have used the IsNumeric() function to check if any strings such as ‘123456’ are true numbers or not. The function seems to be very convenient and easy to use. However, more often than not, you will get errors in your ETL packages when loading these fields into your data warehouse where these fields are expected to be Integer (or bigint or any other numeric ) data type.

You are sure that you have used the IsNumeric() function to exclude those that are not numeric. So what went wrong?

It turned out that the IsNumeric() function is a very forgiven function, while the Cast() (or convert() or the implicit conversion) function is not.

Use Pattern Matching with LIKE keyword

Check out this MSDN article, Pattern Matching in Search Conditions. There are 4 wildcards that we can use with the LIKE keyword.

Using the last one [^], together with the first one %, the following pattern would only allow numbers 0-9 and a decimal point in the string.

‘%[^0-9.]%’

I ran the following test to compare the IsNumeric() function with a pattern matching using the LIKE keyword.

The last one with two decimal points has passed the pattern test. Feel free to experiment to see if you can use pattern matching to eliminate it.

LIKE keyword in T-SQL doesn’t support regular expression

The LIKE (or the CHARINDEX) keyword in T-SQL doesn’t support regular expression, but the pattern matching can be a good alternative when the IsNumeric() function is just not enough.

Replace white spaces with empty characters

You can also try to combine the pattern matching with replacing white spaces with empty characters. Tab, line feed and carriage return control characters have the values CHAR(9), CHAR(10) and CHAR(13) respectively.

cast(REPLACE(REPLACE(REPLACE(your_number_column,CHAR(9),”), CHAR(10),”),CHAR(13),”) as bigint)