Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

SQL #54–Casting numbers with white spaces as Integer will fail

Table of contents

IsNumeric() funciton is too forgiven
Use Pattern Matching with LIKE keyword
LIKE keyword in T-SQL doesn’t support regular expression
Replace white spaces with empty characters

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.

image

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.

image

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)


Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...