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)')
Post a Comment