CODEWITHSUNDEEP
sqlsql-servert-sqlsqldatatypes
palum
palum

Reputation: 233

compare decimal values in varchar column

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

XYZ
10.2
9.0
97.32

When I write a case statement something like

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?

Upvotes: 2

Views: 14964

Answers (4)

palum
palum

Reputation: 233

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453328

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

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)

Upvotes: 0

Andriy M
Andriy M

Reputation: 77687

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.

Upvotes: 2

Jody
Jody

Reputation: 8291

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

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).

Upvotes: 2

Related Questions