String Manipulation: Tips and Techniques, Part 2

Abstract

According to logical query processing in SQL, the WHERE filtering phase is supposed to be evaluated prior to the SELECT phase, so you assume that the conversions should take place only after integer values have been filtered. According to standard SQL, this query isn't supposed to fail.

Headnote

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 be greater than the maximum INT value 2147483647 or less than the minimum -2147483648. Namely, it could successfully convert to a NUMERIC(38, 0) type but not to INT. So the last predicate converts it to NUMERIC(38, 0) and checks that the result falls within the range supported by INT. If all three predicates evaluate to true, the THEN clause can safely convert the input to INT. Otherwise, the implicit ELSE NULL will have the CASE expression return a NULL. The solution is a bit convoluted, as you can see.

In SQL Server 2012, life is good and simple. To achieve the same task, you simply use a new function called TRY.CONVERT, like so:

If the input is convertible to the target type, the function converts it; otherwise, it returns a NULL.

Avoiding Conversion Failures In Dynamic Schema Scenarios

There might also be cases in which you need to convert values from a character string to a different type as part of a query in a dynamic schema scenario. Avoiding conversion failures in those cases isn't always a trivial thing. To demonstrate what I mean, I'll use a table called Properties that the code in Listing I creates and populates with sample data. This table holds object property values.

As you can sec in Listing 1, the val attribute is of a VARCH AR(500) data type, and the type attribute indicates the data type of the value. You're guaranteed that the value is convertible to the data type indicated by the attribute type. So. for example, if the type is TNT, the value is convertible to INT.

Suppose that you need to query all rows where the type is of the integer family (TINYINT, 'SMALLINT, TNT, 'BIGINT), and out of all integer values, filter only those that arc greater than 10. You issue the following query:

You expect to get the output in Table I.

You assume that SQL Server supports a short circuit concept: First, it evaluates the predicate type IN (TINYINT, 'SMALLINT, TNT, 'BIGINT), and only if it's true, proceeds to evaluate the predicate CAST(val AS BIGINT) > 10. You assume that if the first predicate isn't true, there's no reason for SQL Server to proceed to the second predicate, and hence no reason for it to fail on a conversion error.

However, SQL supports a concept called all-at-once, meaning that all expressions that appear in the same logical query processing phase (e.g., the WHERE filtering phase) are conceptually evaluated at the same point in time. This means that SQL Server can start with whichever predicate it prefers, not necessarily in left to right order. So SQL Server might choose which predicate to start with based on optimization considerations. In other words, this code might fail, and if it does, it's not considered a bug. Sure enough, when I ran this code on my system, it failed with the error that Figure 1 shows.

The fix that will let you avoid conversion errors is to use the techniques I described in the previous section. In SQL Server 2008 R2 and earlier, you can use a CASE expression and actually perform the conversion only if you know that the type is of the integer family, or otherwise return a NULL. Here's the revised query code:

In SQL Server 2012, the solution is much simpler using the new TRY_CONVERT function, like so:

Suppose that you decide to define a view that filters only integer properties, like the one in Listing 2.

According to logical query processing in SQL, the WHERE filtering phase is supposed to be evaluated prior to the SELECT phase, so you assume that the conversions should take place only after integer values have been filtered. You then submit the following query against the view:

According to standard SQL, this query isn't supposed to fail. But for optimization reasons, SQL Server expands the definition of the view and rearranges the logic to query the underlying table directly. So this query actually translates to:

You already know that this form might fail.

To fix this problem in SQL Server 2008 R2 and earlier, you need to alter the view's definition to use a CASE expression, as in Listing 3. Try the same query again, and this time it shouldn't fail:

In SQL Server 2012, in the view definition, you can use the simpler fix with the TRY_CONVERT function, as in Listing 4.

Type Conversion Made Simple

In SQL Server 2008 R2 and earlier, converting a character string input to a different type isn't a trivial task when there's a possibility for the value not to be convertible. You can use a CASE expression with some tricky logic to first check whether the value is convertible, and only when ensuring that it is, actually convert. SQL Server 2012 simplifies this need by introducing the TRY_CONVERT function. If the value isn't convertible to the target type, the function returns a NULL, as opposed to generating an error. You can also use a CASE expression or the TRY_CONVERT function to get around conversion failures in dynamic schema scenarios.

InstantDoc ID 139782

Sidebar

MORE on the WEB

Download the listing at InstantDOC ID 139782.