Add a is_valid_convert function - by Erland Sommarskog

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


64
0
Sign in
to vote
ID 354766 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 7/2/2008 1:41:07 PM
Duplicates 564027 Access Restriction Public

Description

In many situations when you want to convert a value from one type to another, you don't
want a conversion error to fail your query, but rather you want to use some fallback
value, NULL of whatever. Unfortunately, it is not very simple to determine whether a 
certain will convert or not. T-SQL offers isdate() and isnumeric(), but the latter is completely
useless, since there is no guarantee that 1 means that you can convert to your type.
And isdate() is confined to the old datetime data type only.
Sign in to post a comment.
Posted by Sam Mesh on 7/20/2011 at 1:45 PM
Hello Microsoft,

I'd like to note that CAST('1.0' as int) is not ANSI compliant in SQL10:
- http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/af3eff9c-737b-42fe-9016-05da9203a667/

How about CAST() and TRY_CONVERT() in SQL11?
Posted by Microsoft on 7/13/2011 at 4:45 PM
Hello Erland & Everyone,
I am happy to announce that SQL Server Denali has added new conversion functions that will address lot of your needs. Please see the documentation link for the latest CTP for more details:

http://msdn.microsoft.com/en-us/library/hh231076(v=SQL.110).aspx

--
Umachandar, SQL Programmability Team
Posted by Peter11111 on 7/13/2011 at 2:00 AM
See TRY_CONVERT in Denali CTP3 : http://msdn.microsoft.com/en-us/library/hh230993%28v=SQL.110%29.aspx
Posted by Solomon Rutzky on 4/10/2011 at 3:50 PM
For anyone that is looking for something more immediate, I have added an IsValidConvert function to my SQLCLR library - SQL# (http://www.SQLsharp.com/). I took a slightly different approach in that I allow for 1 OR MORE datatypes to be tested with the input string. I was not able to implement ConvertElseNull as the "object" .Net type which maps to sql_variant does not allow for NULLs. So I guess we will need to wait on Microsoft for the ConvertElseNull function.
Posted by Sam Mesh on 3/30/2011 at 1:27 PM
> Posted by Microsoft on 7/7/2008 at 8:39 AM
> I've added it to our list of features for SQL-11. This one seems like it should not require much effort to implement; yet should yield immediate benefit for improved error detection/handling. It's surprising how long we've gone without such a basic feature!

Any updates/workarounds?
Posted by GilaMonster on 4/10/2009 at 6:32 AM
Personally I'd love to see both CONVERT_ELSE_NULL and IS_VALID_CONVERT. There are times that I just want to see rows that won't convert and there are times that I want to convert regardless of the mess that's in the column
Posted by DMulvena on 11/2/2008 at 3:33 PM
I'd prefer the cast_else_null option. Alternatively, Simon Sabin has also proposed (261342) the adoption of regular expressions. Regex substitution could be a way to dynamically pick out the values to convert or to provide an alternative.
Posted by Matham on 7/9/2008 at 6:07 AM
I also would prefer either Convert_Else or Convert_Else_Null, over Is_Valid_Convert. Convert_Else would be best.
Posted by Steve Kass on 7/8/2008 at 1:38 PM
There is another reason to prefer CONVERT_ELSE_NULL. With it, we could kill two birds with one stone (or at least a bird and a half), when is_valid_convert only kills one bird.

The reason is that SQL Server may evaluate SELECT list expressions for rows that have not been selected (i.e., that do not satisfy the WHERE clause). This has been noted in the last several years, and new query optimization paths may be why.

For example, on RC0, this batch fails with a conversion error, despite the fact that CONVERT is only applied to values of dt_str that are valid datetime strings. (It also fails if more sensibly written with the convert in the CTE, but this makes my point more clearly.)

with Tsafe(dt_str) as (
select dt_str
from T
where isdate(dt_str) = 1
)
select convert(datetime,dt_str), OrderID
from Northwind..Orders inner hash join Tsafe
on ShippedDate = convert(datetime,dt_str)
and (
    convert(datetime,dt_str) like '1996%'
    or year(convert(datetime,dt_str)) = 1996
)
go

I can only assume this behavior would apply to is_valid_convert the same as with isdate(), so while is_valid_convert is useful, it misses the opportunity to facilitate what is at the root of why IS... functions are useful.

If we had convert_else_null, this obstacle would be gone. With is_valid_convert, we still must protect the CONVERT in its own CASE expression with another call to is_valid_convert. For example, this succeeds with no error:

with Tsafe(dt, dt_str) as (
select
    case when isdate(dt_str) = 1 then convert(datetime,dt_str) end,
    dt_str
from T
where isdate(dt_str) = 1
)
select dt, OrderID
from Northwind..Orders inner hash join Tsafe
on ShippedDate = dt
and (
    dt like '1996%'
    or year(dt) = 1996
)

For the record, I believe the current behavior is a violation of the standard, though it is arguably a murky area. The standard explicitly allows something similar, which is the implementation-dependence of order of evaluation of expressions in places like WHERE clauses, or in SELECT lists for rows that are returned. I don't think the standard allows SELECT list expressions to be evaluated on rows that are not in the table source, however.

SK
Posted by Louis Davidson on 7/8/2008 at 9:00 AM
Yes, I just realized that convert already has a style parameter so my example wouldn't exactly work... But something like that :)
Posted by Louis Davidson on 7/8/2008 at 8:58 AM
How about instead of NULL, which is limiting, just CONVERT_ELSE, so

SELECT CONVERT_ELSE(int,'No Way',1)

would return 1
Posted by Microsoft on 7/7/2008 at 8:39 AM
Hi Erland,

Thankyou for this suggestion. I've added it to our list of features for SQL-11. This one seems like it should not require much effort to implement; yet should yield immediate benefit for improved error detection/handling. It's surprising how long we've gone without such a basic feature!

[I did wonder about an alternate approach - a model where all errors throw exceptions - similar to .NET. However, that won't work for SQL code - since we don't always want to abort the current transaction. I suppose we could add "resumption semantics" - fix up the error, then resume execution from the point of throw - like Win32 supports. But this would be costly to implement - and it's quite tricky to understand]

Thanks,

Jim
Posted by Jacob Sebastian on 7/2/2008 at 7:13 PM
I feel that the CONVERT_ELSE_NULL() option would be great. I do quite a lot of data imports and spend a lot of time performing custom validations to make sure that the input values are correct and can be converted to the target data type. CONVERT_ELSE_NULL() will certainly make this entire process lot easier.
Posted by Steve Kass on 7/2/2008 at 3:06 PM
I should have added that if possible, CONVERT_ELSE_NULL would be introduced simultaneously with CAST_ELSE_NULL, to avoid having to rewrite CAST parameters as CONVERTs when modifying existing code to use this.
Posted by Steve Kass on 7/2/2008 at 3:03 PM
I agree that there is a need to determine whether or not a CAST will succeed. A possible variation on Erland's idea would be CONVERT_ELSE_NULL(), which is identical to CONVERT for valid inputs, and which returns NULL otherwise. This would allow users to produce any value (NULL or otherwise) in situations where an input is unconvertable: for NULL, CONVERT_ELSE_NULL(...) would suffice, and for non-NULL, COALESCE(CONVERT_ELSE_NULL(...),@special).