Sybase IQ：将字符串转换为 NUMERIC，没有错误/异常

发布于 2024-12-05 19:49:53 字数 439 浏览 0 评论 0原文

我正在使用 Sybase IQ，需要将字段从字符串转换为数字。该字段有时包含数字以外的字符。在这些情况下，我希望它返回 0 而不是引发异常。怎么办呢？

您可以查看下面的语句来了解更多信息：

SELECT CONVERT(NUMERIC(10, 0), '');
SELECT CONVERT(NUMERIC(10, 0), '1');
SELECT CONVERT(NUMERIC(10, 0), 'a');
SELECT CONVERT(NUMERIC(10, 0), 'a1');
SELECT CONVERT(NUMERIC(10, 0), '1a');

只有前两行才有效。其余 3 行将引发异常：

Cannot covert a to a NUMERIC(10, 0)(07006,-157)

提前致谢！

原文

I'm using Sybase IQ and need to convert a field from string to NUMERIC. The field sometimes has characters other than digits. In those cases I want it to return 0 instead of raising exceptions. How to do so?

You may look at the statements below to understand more:

SELECT CONVERT(NUMERIC(10, 0), '');
SELECT CONVERT(NUMERIC(10, 0), '1');
SELECT CONVERT(NUMERIC(10, 0), 'a');
SELECT CONVERT(NUMERIC(10, 0), 'a1');
SELECT CONVERT(NUMERIC(10, 0), '1a');

Only the first 2 lines will work. The remaining 3 lines will raise exceptions:

Cannot covert a to a NUMERIC(10, 0)(07006,-157)

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

折戟 2024-12-12 19:49:53

我不确定是否有更好的方法，但你可以尝试一下。

SELECT CASE PATINDEX('%[a-zA-Z]%','') 
   WHEN 0 THEN CONVERT(NUMERIC(10,0), '') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a1') 
   ELSE 0 END

希望它有帮助:)

I am not sure if there is a better way but you could try this.

SELECT CASE PATINDEX('%[a-zA-Z]%','') 
   WHEN 0 THEN CONVERT(NUMERIC(10,0), '') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','1a') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), '1a') 
   ELSE 0 END
SELECT CASE PATINDEX('%[a-zA-Z]%','a1') 
   WHEN 0 THEN CONVERT(NUMERIC(10, 0), 'a1') 
   ELSE 0 END

Hope it helps :)

回复收藏 0 原文

~没有更多了~