How to prevent Sybase from eliminating spaces in a characters fields

Question

I am facing this issue on sybase.

update tst_table set col1 = 'abc  ' 

the result will be

abc

Sybase will automaticly eliminate the spaces. However I am migrating, so I cannot add the spaces manually.

Answer 1

You should change tst_table.col1 from varchar type to char type. Varchar always truncate spaces but char left spaces to all gap for example string abc with 2 spaces. You can also use text type to store whole string with spaces.

TYPE         VALUE   
-------------------------
varchar(10)  'abc'
char(10)     'abc       ' <----------- 3 chars abc + 7 spaces 
text         'abc  '

Below code modify column datatype to char(10)

alter table  tst_table
    modify col1 char(10) null

As documentation said

You cannot modify a column to either text or image datatype.

---

One more solution is to use reverse function. For example you have a table tab:

create table tab
(
  var varchar(100)    
)

and insert data should be:

insert into tab
select reverse('abc  ')

if you make select

select var from tab

you will see the data is

'  cba'

so should take data like this

select reverse(var) from tab

But it will work only for strings like 'abc ' but it doesn't work for strings ' abc' and ' abc '.