MS SQL server casting without exception

Question

Is there any "convert" function in MS SQL server that allows to cast types safely(without throwing exception). I need something like "tryParse" in C# lang but as SQL statement.

More detailed, I need the following statement returns zero or any else but throwing exception.

select convert(float, 'fjsdhf')

thanks in advance.

Answer 1

This will default non-numerics to 0 and will not require another statement:

 SELECT CASE      WHEN ISNUMERIC(myvarcharcolumn)=1 THEN         CONVERT(float, REPLACE(LTRIM(RTRIM(myvarcharcolumn)), ',', '.'))      ELSE 0 END AS myfloatcolumn 

The REPLACE() function call is used to change commas to periods. Commas are used in some cultures as a decimal separator (e.g., "1,25" instead of "1.25"), but unless your server is set up with one of those as the default culture, ISNUMERIC() will return 1 but CONVERT() will throw an error. This does mean that your strings should not use commas as thousands separators, but in most cases, a comma for a decimal placeholder is more likely to be a decimal placeholder.

The LTRIM(RTRIM()) call is because ISNUMERIC() will return 1 for a string with leading or trailing spaces, but CONVERT() can't deal with them. So, you must trim your strings.

The only remaining potential issue is that ISNUMERIC() will return 1 if the number can be represented as an int, currency, decimal, or float, but you're only converting to a float. Realistically, a float can store just about anything you throw at it, but if you were trying to convert to an int instead, ISNUMERIC() would return 1 for a value like "2.5", but CONVERT(int, '2.5') will still throw an error.

Answer 2

In SQL SERVER 2012 there are new functions to deal with this

try_cast try_convert try_parse