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
Advertisement
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;
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.