Monday, September 21, 2020

SQL: TRY_CAST alternative for old versions of SQL Server

In modern versions of Microsoft SQL Server there is a TRY_CAST function which returns NULL if the type cast fails, and returns the converted value if it succeeds. This is helpful if you're trying to format numbers from non-sanitized input and somebody put malformed data into a field.

At my job, I'm still dealing with SQL Server 2005 which does not provide this function. An alternative that I found online was to use XML to accomplish the same result:

CAST('' AS XML).value('sql:column("ItemHeight") cast as xs:decimal ?', 'decimal(28,10)')

No comments:

Post a Comment