In my current position, I deal with a lot of data quality issues. This column should be a date, but we get an integer, string, NULLs, etc. As a rule of our processing, if we cannot convert the value to the appropriate data type, then we use a default value, or choice of values depending on the situation.
SQL Server already has the CAST and CONVERT functions built in. There is a shortcoming of these functions though. If the value cannot be converted, you get an error and your processing stops. For converting values, you have to pass through the table twice. Once to manually convert any known values that cannot convert into something that can or set them to NULL. Then you can run CAST or CONVERT on the values. We wanted to be able to do this in one step and this led us to .NET’s TryParse() method.
Each basic data type in .NET has a TryParse() method that attempts to parse the value and returns a boolean if successful. The parsed value is passed back through an output variable. In my case, I don’t really care about the boolean return value. I want a value if it was successful, otherwise give me a NULL. This is the syntax for creating the SQL CLR version.
And the syntax for using it within SQL.
Why would I want NULL on failure you might ask. Well, if I get a NULL back, then I can easily check against NULL for success and use functions such as COALESCE to substitute a default value if the parsing failed. A good case for us to use this is when we have blank strings that need to convert into a default date. CONVERT cannot cast the value ” to a datetime and thus throws an error.
Now I can do things like the following to allow processing to complete if CONVERT hits a bad value.
Now the drawbacks of using this method. If you need to make sure you have all valid values beforehand, this will cause those to fail silently. An alternative would be to use this to check for invalid values. You can do something like this to find problematic values:
This allows you to find all unconvertible values without SQL throwing an error as it would if you had tested with CONVERT(). We mainly use this function in the second situation. We want to know all values that would cause problems.
I have also made versions for Int, BigInt and Money following the same basic pattern. As with any CLR functions, make sure you know the security and performance implications when using it. Hopefully if you do a lot of data staging then converting this could make your life easier.