Hi Andrus,
Hi!
>> In case of varchar field values will appear in database sometimes with
>> trailing spaces and sometimes without.
>> This requires major application re-design which much is more expensive than
>> continuing using char fields.
>A simple BEFORE INSERT OR UPDATE trigger would take care of that.
Changing char to varchar will break commands where trailing space is used in comparison.
For example query
create table test ( test char(10) );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space
does not return data anymore if your recommendation is used:
create table test ( test varchar );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space
In production 'test ' is query parameter coming from application with possible trailing space(s).
Adding trigger does not fix this.
How to fix this without re-writing huge number of sql commands?
In the end, your question is more at application level than database itself. The real question is: which one is correct? With or without trailing space?
If you decide that it's without, you could apply a TRIM in a trigger on each INSERT and UPDATE. Then, you could replace the table by a view of the same name and implement the TRIM on SELECT there. This way, you don't have to touch anything in the application.
Hope it helps
Olivier