MS SQL server casting without exception.

Question

Hello world!

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

You can test that a value is numeric with the TSQL function ISNUMERIC()

http://msdn.microsoft.com/en-us/library/ms186272.aspx

And, in case you aren't already aware of it, TSQL now has a TRY CATCH construct.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

Answer 2

If the version of SQL you are using supports the CLR you can write TryParse style methods.\ It doesn't meet your criteria of adding custom functions to the db though. But it's probably going to be faster than a SQL UDF.

something like

[SqlFunction]
public static SqlDouble TryParseDouble(SqlString str)
{
  Double d;
  bool success = Double.TryParse(str, out d);
  if (!success)
    return SqlDouble.Null;
  return new SqlDouble(d);
}

Answer 3

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

 SELECT CASE 
    WHEN ISNUMERIC(myvarcharcolumn) 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.