Q:

Try_Convert for SQL Server 2008 R2

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.

A:

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

A:

Since this is to be a permanent change, then I'd do it as a two step process - first, remove the invalid text, then convert the column.
To remove the invalid text, I'd do something like:
UPDATE [Table]
SET [Column] = NULL
WHERE [Column] LIKE '%[^0-9.]%' or
LEN([Column]) - LEN(REPLACE([Column],'.','')) > 1 or
LEN([Column]) > 28

After that's done, everything that remains should be convertible just by changing the column definition
ALTER TABLE [Table] ALTER COLUMN [Column] decimal(28,10)

A:

You can write your own custom parser in C# and use SQLCLR, using for example Decimal.Parse(). Do not atempt to use ISNUMERIC, is notoriously incorrect (returns TRUE for strings that fail to CAST).

