CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin
Justin

Reputation: 269

SQL cast char as number

I get this error:

Conversion failed when converting the varchar value 'This is a FUTURES item. ' to data type int.

What's wrong with the CAST?

UPDATE STOCK 
SET DESC2 = 'This is a FUTURES item. ' + LEFT(DESC1, 4) + 'futures will begin arriving in ' + (CAST(LEFT(DESC1, 4) as INT) + 3)
WHERE ISNUMERIC(LEFT(DESC1, 4)) = 1

Upvotes: 1

Views: 768

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The operator + is overloaded. When any operand is a number, then it is treated as addition. Given what you want to do, you need to re-convert the value back to a string:

UPDATE STOCK
    SET DESC2 = 'This is a FUTURES item. ' + LEFT(DESC1, 4) + 'futures will begin arriving in ' + CAST(CAST(LEFT(DESC1, 4) as INT) + 3 as VARCHAR(255))
WHERE ISNUMERIC(LEFT(DESC1, 4)) = 1;

Although this query will probably work, there is no guarantee (in SQL Server) that the WHERE occurs before the set. That means that you could still get a type conversion error. The solution in more recent versions is try_convert(). Or, you can use a case statement:

UPDATE STOCK
    SET DESC2 = 'This is a FUTURES item. ' + LEFT(DESC1, 4) + 'futures will begin arriving in ' +
                (CASE WHEN ISNUMERIC(LEFT(DESC1, 4)) = 1
                      THEN CAST(CAST(LEFT(DESC1, 4) as INT + 3) as VARCHAR(255))
                 END)
WHERE ISNUMERIC(LEFT(DESC1, 4)) = 1;

Upvotes: 3

Related Questions