Try_Convert for SQL Server 2008 R2

Question

I'm using SQL Server 2008 R2 and have a VARCHAR column I want to convert to DECIMAL(28,10) using CONVERT. But many of those rows are badly formatted, so it is not possible to parse them to a number. In that case I just want to skip those by setting result to 0 or NULL.

I know there is a new statement in SQL Server 2012 (TRY_CONVERT()) that would be handy.

Is this possible in 2008 or must I wait until we update to next version SQL 2012?

EDIT

Unfortunately ISNUMERIC() is not reliable in this case. I tried

ISNUMERIC(myCol) = 1 

That returns true for rows that CONVERT is not able to convert to DECIMAL.

Answer 1

When using XML in SQL Server you can try to cast to a data type and receive null values where the cast fails.

declare @T table (   Col varchar(50) )  insert into @T values ('1'), ('1.1'), ('1,1'), ('1a')  select cast('' as xml).value('sql:column("Col") cast as xs:decimal ?',                               'decimal(28,10)') as Col from @T 

Result:

Col ------------- 1.0000000000 1.1000000000 NULL NULL