Convert CHAR to int in SQL results in error

I’m using Toad Data Point and am having an issue converting a Column. I’m trying to convert a Column called Rep which contains Char(2) into an integer. The table looks like this:

Row_ID   Rep   
1        00
2        01
3        02
4        03

​x
 
Row_ID   Rep   1        002        013        024        03​

I’ve tried the following:

drop table if exists #my_table
select Row_ID, CONVERT(INT, Rep) as new_col
into 
#my_table
from original_table

 
drop table if exists #my_tableselect Row_ID, CONVERT(INT, Rep) as new_colinto #my_tablefrom original_table​

I tried CAST(Rep as INT) as well and both give me the error Sybase Database Error: Data Exception -date type conversion is not possible.

Thanks

Answer

In Sybase, something like this should work:

select Row_ID,
       (case when rep not like '%[^0-9]%' then cast(rep as int) end) as new_col
into #my_table
from original_table;

 
select Row_ID,       (case when rep not like '%[^0-9]%' then cast(rep as int) end) as new_colinto #my_tablefrom original_table;​

The case expression is checking that the column rep only consists of digits — that is, it has no non-digit character. This does not handle negative numbers, but similar logic would handle that.

Advertisement

Answer