Error converting varchar to numeric with MSSQL

Question

Query

SELECT TOP 1000
CASE WHEN VRI.Square_Footage <> '' 
THEN VRI.Square_Footage 
ELSE 
    CASE WHEN VRI.Property_Type = 'LAND' 
        THEN CAST((CONVERT(NUMERIC(38, 3),VRI.Acres)*43560) AS DECIMAL) 
    ELSE 
        VRI.Lot_Size 
    END 
END
FROM View_Report_Information_Tables AS VRI

Even if I checked for VRI.Acres with isnumeric(), it still yield the same exact error? How can I fix this problem?

@Conrad Frix: Thanks for the edit! Btw, you have a very cool avatar ;)! — roxrook
– roxrook, Commented Apr 25, 2011 at 16:34

Martin Smith · Accepted Answer · 2011-04-25 16:43:03Z

2

ISNUMERIC doesn't guarantee that it will successfully cast to decimal.

SELECT ISNUMERIC('£100') /*Returns 1*/

SELECT CONVERT(NUMERIC(38, 3),'£100') /*Error*/

Additionally all branches of the case statement need to return compatible datatypes. It looks like VRI.Square_Footage is a string.

Does this work for you?

SELECT TOP 1000 CASE
                  WHEN ISNUMERIC(VRI.Square_Footage + 'e0') = 1 THEN
                  VRI.Square_Footage
                  WHEN VRI.Property_Type = 'LAND'
                       AND ISNUMERIC(VRI.Acres + 'e0') = 1 THEN CAST((
                  CONVERT(NUMERIC(38, 3), VRI.Acres) * 43560 ) AS DECIMAL)
                  WHEN ISNUMERIC(VRI.Lot_Size + 'e0') = 1 THEN VRI.Lot_Size
                END
FROM   View_Report_Information_Tables AS VRI

edited Apr 25, 2011 at 16:43

answered Apr 25, 2011 at 16:20

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

roxrook Over a year ago

@Martin: Thanks. Yes, VRI.Square_Footage is actually varchar. How can I work around this issue?

Jerry Ritcey Over a year ago

This. You can't mix strings and numbers in the same column. One of them must be converted to the other.

roxrook Over a year ago

@Jerry Ritcey: So that means I have to convert all varchar to decimal?

Martin Smith Over a year ago

@Chan - The only way of mixing (non numeric) strings and numbers would be a cast to sql_variant or cast the numbers as strings. Can you provide an example of the data that fails?

roxrook Over a year ago

@Conrad Frix: I would agree with you. Unfortunately, this belongs to SQL team, I'm not in charge of it. Btw, I just converted it back to varchar and the sql query run fine.

|

SQLMenace · Accepted Answer · 2011-04-25 16:24:02Z

2

Here run this

 SELECT ISNUMERIC('d25')

That can be converted to float but not decimal/numeric

SELECT CONVERT(FLOAT,'2d5')

As you can see, you can't depend on numeric for decimal/numeric data types

Take a look at IsNumeric, IsInt, IsNumber for some code that will show you how you can check

answered Apr 25, 2011 at 16:24

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Collectives™ on Stack Overflow

Error converting varchar to numeric with MSSQL

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related