CODEWITHSUNDEEP
sql-servert-sqlexception
AndrewG
AndrewG

Reputation: 1429

MS SQL server casting without exception

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.

Upvotes: 36

Views: 33033

Answers (6)

MJoy
MJoy

Reputation: 1369

Try this, 

DECLARE @SoMe1 FLOAT = 100.99;

DECLARE @SoMe2 VARCHAR(10) = 'TEXT HERE';

SELECT CASE WHEN TRY_CONVERT(FLOAT, @SoMe1) IS NULL THEN 0 ELSE @SoMe1 END
AS Rslt;      -- Output will be 100.99

SELECT CASE WHEN TRY_CONVERT(FLOAT, @SoMe2) IS NULL THEN 0 ELSE @SoMe2 END
AS Rslt;      -- Output will be 0

Upvotes: 2

Christian Kiewiet
Christian Kiewiet

Reputation: 880

FWIW, considering this question is almost 6 years old: In SQL Server 2012 you can use TRY_CONVERT which will return NULL on error; which is probably precisely what you want.

Upvotes: 1

richardtallent
richardtallent

Reputation: 35374

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.

Upvotes: 36

Devin Garner
Devin Garner

Reputation: 1406

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

try_cast try_convert try_parse

Upvotes: 14

Jafin
Jafin

Reputation: 4381

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);
}

Upvotes: 3

Ed Guiness
Ed Guiness

Reputation: 35267

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

Upvotes: 9

Related Questions