MS SQL服务器无一例外地投射 [英] MS SQL server casting without exception

问题描述

MS SQL Server中是否有任何转换功能，允许安全地转换类型（不抛出异常）。我需要像C＃lang中的tryParse一样，但是作为SQL语句。

更详细的说，我需要以下语句返回0或者抛出异常。 >

选择转换（float，'fjsdhf'）

提前感谢。 >

解决方案

这将默认非数字为0，不需要另一个语句：

  SELECT CASE 
 WHEN ISNUMERIC（myvarcharcolumn）= 1 THEN 
 CONVERT（float，REPLACE（LTRIM（RTRIM（myvarcharcolumn）），'，'，'。 ））
 ELSE 0 END AS myfloatcolumn 
  

REPLACE（）函数调用用于更改逗号到期。逗号在一些文化中用作小数分隔符（例如，1,25而不是1.25），但除非您的服务器设置为默认文件之一，否则ISNUMERIC（）将返回1，但CONVERT ）会抛出一个错误。这个 意味着您的字符串不应该使用逗号作为数千个分隔符，但在大多数情况下，小数占位符的逗号更有可能是小数占位符。

LTRIM（RTRIM（））调用是因为ISNUMERIC（）将为具有前导或尾随空格的字符串返回1，但CONVERT（）无法处理它们。所以，你必须修剪你的字符串。

唯一剩下的潜在问题是ISNUMERIC（）将返回1，如果该数字可以表示为int，currency，decimal，或者float，但是你只能转换成float。实际上，浮点数可以存储任何你抛出的东西，但是如果你尝试转换为int，ISNUMERIC（）将返回1，如2.5，但CONVERT（int，'2.5'）将会仍然会出错。

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.

解决方案

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.

这篇关于MS SQL服务器无一例外地投射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！