Hi Andrus,
Le sam. 4 avr. 2020 à 10:09, Andrus <kobrule...@hot.ee> a écrit :
> 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
>
