比较varchar列中的十进制值

I have a varchar column by name XYZ and the values in that column are like

我有一个名为XYZ的varchar列，列中的值类似

XYZ
10.2
9.0
97.32

When I write a case statement something like

当我写一个case语句时

CASE 
    WHEN XYZ > '9.0' THEN
    'DONE'
    WHEN XYZ < '7.0' THEN
    'NOT DONE'
    WHEN XYZ BETWEEN '7.0' AND '9.0' THEN
    'NULL'
     END

It is not comparing with the values. What should be done to fix this?

它不是与值的比较。应该做些什么来解决这个问题?

4 个解决方案

#1

You need to determine whether the data is numeric before doing the cast. Denali has a TRY_CONVERT function but in previous versions you need to use a CASE WHEN ISNUMERIC(XYZ + 'e0') = 1 statement which could either be incorporated into the main CASE or separated out as below

在执行强制转换之前，需要确定数据是否为数值。Denali有一个TRY_CONVERT函数，但是在以前的版本中，当ISNUMERIC(XYZ + 'e0') = 1语句可以合并到主格中，也可以像下面这样分开

DECLARE @YourTable TABLE
(
XYZ VARCHAR(50)
)

INSERT INTO @YourTable
SELECT 'foo' AS XYZ
UNION ALL 
SELECT '8.9'
UNION ALL 
SELECT '9.1'
UNION ALL 
SELECT '6.5'

SELECT 
CASE 
    WHEN _XYZ > 9 THEN
    'DONE'
    WHEN _XYZ < 7 THEN
    'NOT DONE'
     END
FROM @YourTable
CROSS APPLY(SELECT CASE WHEN ISNUMERIC(XYZ + 'e0') = 1 
            THEN CAST(XYZ AS NUMERIC(18,8)) END) CA(_XYZ)

#2

if you know the data is clean, you can run a cast (or convert) statement on the field.
CASE WHEN CAST(XYZ AS decimal) > 9.0 THEN 'DONE' WHEN CAST(XYZ AS decimal) < 7.0 THEN 'NOT DONE' WHEN CAST(XYZ AS decimal) BETWEEN 7.0 AND 9.0 THEN 'NULL' END

如果知道数据是干净的，可以在字段上运行cast(或convert)语句。当转换(XYZ为十进制)> 9.0时，当转换(XYZ为十进制)< 7.0时，当转换(XYZ为十进制)时，当转换(XYZ为十进制)在7.0至9.0之间时，当转换(XYZ为十进制)时，当转换(XYZ为十进制)时，当转换(XYZ为十进制)时，则“完成”

Note that you should lose the quotes around the '9.0' Also of note, the cast operation is going to be run against the entire field. This will make the use of indexes useless as it has to scan the entire table to make the conversion before it can make the comparison. This can have serious performance implications for even moderately large tables (ie 10-100k records).

注意，您应该在'9.0'附近丢失引号，注意，cast操作将针对整个字段运行。这将使索引的使用变得无用，因为在进行比较之前，它必须扫描整个表以进行转换。这甚至会对较大的表(即10-100k记录)产生严重的性能影响。

#3

If the column indeed represents only numbers and is never expected to contain anything else but numbers, then just do a one-time operation of converting the column to a more appropriate type. Depending on the nature of the data, you might have to choose between precise and approximate types, namely, decimal and float/real. For the former you'd also have to choose the right precision and scale (the number of digits after the decimal point).

如果列实际上只表示数字，并且不希望包含除数字以外的任何内容，那么只需执行一次操作，将列转换为更合适的类型。根据数据的性质，您可能需要在精确类型和近似类型之间进行选择，即十进制和浮点/实数。对于前者，您还必须选择正确的精度和比例(小数点后的位数)。

The conversion itself would look basically like this:

皈依本身基本上是这样的:

ALTER TABLE atable
ALTER COLUMN XYZ decimal(15, 2)

Again, you will need to make sure there are no non-number values in the column, otherwise the statement is going to fail.

同样，您需要确保列中没有非数字值，否则语句将失败。

#4

WHEN ISNUMERIC(XYZ) = 0 
                                THEN 'DONE'
                WHEN CONVERT(DECIMAL,XYZ) > 9 
                                THEN 'NOT DONE'
                WHEN CONVERT(DECIMAL,XYZ) < 7.0 
                                THEN 'SOMETIMES DONE'
                WHEN CONVERT(DECIMAL,XYZ) BETWEEN 7.0 AND 9.0 
                                THEN 'EVERYTIME DONE'
                WHEN ISNULL(XYZ,'') = '' 
                                THEN 'NONE'
END

yahoo atlast done! This worked out for me. Thanks for your help and useful advice Appretiate all your time.

雅虎论述完成了!这对我来说是行得通的。感谢您的帮助和有用的建议，感谢您一直以来的帮助。

#1

DECLARE @YourTable TABLE
(
XYZ VARCHAR(50)
)

INSERT INTO @YourTable
SELECT 'foo' AS XYZ
UNION ALL 
SELECT '8.9'
UNION ALL 
SELECT '9.1'
UNION ALL 
SELECT '6.5'

SELECT 
CASE 
    WHEN _XYZ > 9 THEN
    'DONE'
    WHEN _XYZ < 7 THEN
    'NOT DONE'
     END
FROM @YourTable
CROSS APPLY(SELECT CASE WHEN ISNUMERIC(XYZ + 'e0') = 1 
            THEN CAST(XYZ AS NUMERIC(18,8)) END) CA(_XYZ)

#2

#3