Search code examples
sqlsybase

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

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

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

Solution

  • 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;

    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.