Content area
Full text
Learn how to handle difficult type conversions
Last month, I started a two-part series about string manipulation tips and techniques. I continue the series this month, focusing on type conversions I discuss how to handle cases in which you need to convert a character string value to another type when there's a possibility for the conversion not to succeed. I show how to handle this need in SQL Server 2008 R2 and earlier, as well as introduce new capabilities in this area in SQL Server 2012.
Converting a Character String to a Different Type
Consider a situation in which you're given a character string input (call it @str) that represents an integer. You're supposed to convert it to an integer typed value The tricky part is that there's no assurance that the string will successfully convert (e.g., because of data quality issues). In case the value isn't convertible, you're supposed to return a NULL. Your solution isn't supposed to generate an error.
In SQL Server 2008 R2 and earlier, this isn't a straightforward task. One solution is to use a CASE expression with predicates in the WHEN clause that ensure that the value is convertible, and only if it is, actually convert it in the THEN clause. Here's how such a CASE expression might look:
The first predicate uses the ISNUMERIC function to ensure that the input is convertible to some numeric type. Note, though, that ISNUMERIC returns I even in eases in which the input isn't convertible to an integer but rather to some other numeric type (e.g., NUMERIC, FLOAT, MONEY). For example, in all of the following three cases, ISNUMERIC returns 1:
The second predicate further verifies that there are no symbols besides digits, plus and minus signs, and spaces. Still, the value could...