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