2013-12-12

SQL 2012 TRY_CONVERT

There're new built-in functions in SQL2012 which can be used to trying convert/cast/parse a value into specified type, and if the conversion can't be done, it won't raise any error but return a NULL value. I found it very handy for parsing XML into SQL data. The example below demonstrate how to use TRY_CONVERT.

SELECT TRY_CONVERT(date, dbo.ufnBlankToNull(T.c.value('./@tradedate', 'varchar(50)')), 120),
TRY_CONVERT(float, dbo.ufnBlankToNull(T.c.value('./@PX_OPEN', 'varchar(50)')))
FROM @x.nodes('/PriceHistory/instrument') AS I(c)
CROSS APPLY I.c.nodes('./trade') AS T(c)

As the TRY_CONVERT function will convert blank string to 0 for numeric type and '1900-01-01' for date type, which isn't what I want. So I create a custom scalar function as below to convert blank string to NULL before input the value into TRY_CONVERT.
CREATE FUNCTION ufnBlankToNull
(
    @s nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    RETURN LTRIM(RTRIM(NULLIF(@s, '')))
END

No comments:

Post a Comment