CODEWITHSUNDEEP
sqlsql-servert-sql
cool breeze
cool breeze

Reputation: 4811

How to safely convert a varchar(255) to an int?

I was using a column in a case expression and it was working fine on server1. When I ran it on server2 it was failing because the column had the value 'false' in it.

The column is a varchar(255), but in my case expression I was using it as if it was an INT type. It worked fine but now it is failing because of the 'false' value in server2.

How can I safely convert to an INT, and if the conversion fails, default to 0.

Is this possible?

My query looks like:

UPDATE t1
     set 
          c1 = ISNULL(
                     (
                       SELECT CASE c2
                            WHEN 123 then 'hello'
                            WHEN 234 then 'bye'
                            ELSE ''
                       END
                     )
                , '')
FROM table1 as t1

Upvotes: 3

Views: 1203

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

Use TRY_PARSE (SQL Server 2012+):

SELECT ISNULL(TRY_PARSE(column_name AS INT),0)
FROM your_table;

LiveDemo

Upvotes: 3

Julien Blanchard
Julien Blanchard

Reputation: 815

There are quite a few ways to convert from numeric to varchar, but none of them are available to you (really, SQLServer2008 is disappointing because it's lacking just a few cool features that are nearly required).

In your case, the best way to do is it simply to convert your numeric expression to varchar like this :

UPDATE t1
     set c1 = CASE c2
         WHEN '123' then 'hello'
         WHEN '234' then 'bye'
         ELSE ''
     END      
FROM @t1 t1

It doesn't answer the question, but it solves your problem.

Upvotes: 4

Related Questions