Menu
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.
332
However, it is still very useful because it allows you to return a default value if CAST is not performed correctly. Your function's dependency on the ISNUMERIC function makes it unreliable. SELECT dbo. TRY_CAST('+', 'INT', 0) will yield Msg 8114, Level 16, State 5, Line 16 Error converting data type varchar to float.
The TRY_CONVERT() function tries to convert the value passed to it to a specified data type. It returns the value as the specified data if the cast succeeds; Otherwise, it returns. However, if you request a conversion that is explicitly not permitted, the TRY_CONVERT() function will fail with an error.
SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
