CAST连字符（ - ）到十进制 [英] CAST hyphen (-) to Decimal

问题描述

我必须在sql server中输入CAST' - '（Hyphen Symbol）到Decimal（8,2）。

I have to CAST '-' (Hyphen Symbol) to Decimal(8,2) in sql server.

我创建了一个CTE并将一些数据一个有一些列的表。对于1列我需要转储' - '作为DECIMAL（8,2）。我将如何实现这个...

I am creating a CTE and dumping some data into a table with some columns. For 1 Column I need to dump '-' AS DECIMAL(8,2). How I will achieve this...

我使用

    SELECT CAST('-' AS DECIMAL(8,2)) Column1

溢出错误将varchar转换为数据类型数字。

Which is throwing Error: Arithmetic overflow error converting varchar to data type numeric.

我知道上面的查询或铸造不能做，但我需要实现它！请帮助

"I know the above query or casting cannot be done", but I need to achieve it somehow!! please help

我有几个查询，使用它们将数据转储到物理表中。此外，第一查询是上面的，其创建具有4列的表。在col1我需要把 - 符号，在其他查询是一个十进制列。第二个查询是将数据插入到同一物理表。在从该动态创建的物理表获取数据期间发生一些计算。这是原因，我需要将' - '（Hyphen）转换为十进制列

I have couple of queries using which I am dumping a data into a physical table. Further to this, first query is the above which is creating a table with 4 columns. in col1 I need to put '-' symbol, which in other query is a decimal 'column'. Second query is inserting the data to the same physical table. there are some calculations happening during the fetching of the data from this dynamic created physical table. This is the reason, I need to convert '-' (Hyphen) into a Decimal column

希望这清除问题!!!

Hope this clears the problem!!!

推荐答案

您的基本问题是什么？您的映射不正确。

What's your base problem? You have a bad mapping.

我们有字符串，我们要做成数字。问题是 - 没有数值。因此，你需要做一些事情。您的问题的问题是，您没有指出在错误值的情况下代码应该做什么。

We have strings we want to make into numbers. The problem is there is no number value for - Therefore, you need to do something about it. The problem with your question, is that you haven't indicated what the code should do in cases of bad values.

• 我们炸了过程和停止将数据存储到表中？


• 您的系统在处理坏数据时使用的哨兵值。也许-1有意义。


• 我们应该将这些行插入到我们的目标表？

如果您使用SQL Server 2012+，我们有 TRY_CAST 方法将尝试进行数据类型转换，如果不能，则该方法返回一个NULL。

If you're using SQL Server 2012+, we have a TRY_CAST method that will attempt to make the data type conversion and if it cannot, then the method returns a NULL.

SQL Server中内置了 IsNumeric 函数，但它不是100％可靠（需要引用）。与 CASE 表达式配对，这将泛化Gordon的答案。

There is a IsNumeric function built into SQL Server but it's not 100% reliable (citation needed). Pair that with a CASE expression and that would genericise Gordon's answer.

否则，您需要添加过滤到您的源查询以过滤掉那些坏行。我遇到了类似情况之前的日期

Otherwise, you'll need to add a filter to your source query to filter out those bad rows. I ran into a similar situation before with dates

这篇关于CAST连字符（ - ）到十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！