Error converting data type varchar to numeric. When try to compare numeric

Question

I got a error when i am trying to execute below query. can anyone sort out this would appreciated.

DECLARE @TABLE TABLE(ID INT,CATEGORY VARCHAR(30),VALUE VARCHAR(30))
INSERT INTO @TABLE
SELECT 1,'A','5'
UNION ALL 
SELECT 2,'B','6'
UNION ALL
SELECT 3,'C','VAL'
UNION ALL
SELECT 4,'D','HSD'

DECLARE @TABLE1 TABLE(ID INT,CATEGORY VARCHAR(30),VALUE VARCHAR(30))
INSERT INTO @TABLE1
SELECT 1,'A','5.0'
UNION ALL 
SELECT 2,'B','6.0'
UNION ALL
SELECT 3,'C','VAL'
UNION ALL
SELECT 4,'D','HSD'

SELECT
    A.ID,
    A.CATEGORY,
    A.VALUE
FROM @TABLE A, @TABLE1 B
WHERE
    CASE ISNUMERIC(A.VALUE) WHEN 1 THEN CAST (A.VALUE AS NUMERIC)
                                   ELSE A.VALUE END=CASE ISNUMERIC(B.VALUE) WHEN 1
                                   THEN CAST (B.VALUE AS NUMERIC) ELSE B.VALUE END

Tim Biegeleisen · Accepted Answer · 2017-08-22 05:56:56Z

I believe the error you are getting is being caused by that your CASE expressions in the WHERE clause have branches with different types, namely text and numeric. To fix this, you need to make all branches be the same type. Making everything numeric won't work because of the text, but we can make everything text instead.

For the case of numeric data, you don't even have the same type of data there either. To get around this, you can cast the numeric data to a common decimal format before converting back again to text:

WHERE
    CASE WHEN ISNUMERIC(A.VALUE) = 1
         THEN CONVERT(VARCHAR, CAST(A.VALUE AS DECIMAL(10,2)))
         ELSE A.VALUE END =
    CASE WHEN ISNUMERIC(B.VALUE) = 1
         THEN CONVERT(VARCHAR, CAST(B.VALUE AS DECIMAL(10,2)))
         ELSE B.VALUE END

The best long term solution for you would be to not mix text and numeric data in the same column. I don't know why this answer was downvoted, as it is working code which answers the original question.

Output:

Demo here:

Rextester

鄭有維 · Accepted Answer · 2017-08-22 06:25:14Z

-2

One Columns can only be one type..

You can't put two type "NUMERIC" and "varchar(30)" in one column...

Best way is cast to one type. Seems varchar(30) is a better choice.

SELECT
    A.ID,
    A.CATEGORY,
    A.VALUE
FROM @TABLE A, @TABLE1 B
WHERE
    ISNUMERIC(A.VALUE) = ISNUMERIC(B.VALUE)
    and 
    CASE ISNUMERIC(A.VALUE) WHEN 1 THEN CAST(CAST(A.VALUE AS NUMERIC) as VARCHAR(30)) ELSE A.VALUE END
    = CASE ISNUMERIC(B.VALUE) WHEN 1 THEN cast(CAST(B.VALUE AS NUMERIC) as VARCHAR(30)) ELSE B.VALUE END

edited Aug 22, 2017 at 6:25

answered Aug 22, 2017 at 5:55

鄭有維

2653 silver badges14 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

I don't think this is what the OP wants, because none of the numeric data is matching: See here.

Collectives™ on Stack Overflow

Error converting data type varchar to numeric. When try to compare numeric

2 Answers 2

Rextester

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related