Trim Trailing Spaces.

[Former Member]

Is there anyway to stop an SQL Anywhere database from storing trailing spaces in the database. I am working on a legacy system where the front end PowerBuilder application doesn't clean up the data before it commits to the database.

[former_member188493 · Accepted solution]

SQL Anywhere will neither trim nor pad values that are stored in the database.

Triggers can be used as a workaround:

CREATE TABLE t ( s VARCHAR ( 10 ) );
CREATE TRIGGER insert_update 
   BEFORE INSERT, UPDATE ON t 
   REFERENCING NEW AS new_t
   FOR EACH ROW 
BEGIN
   SET new_t.s = RTRIM ( new_t.s );
END;


INSERT t VALUES ( 'Hello   ' );
SELECT STRING ( 'INSERT: [', t.s, ']' ) FROM t;
UPDATE t SET t.s = 'World!   ';
SELECT STRING ( 'UPDATE: [', t.s, ']' ) FROM t;


STRING('INSERT: [',t.s,']')
'INSERT: [Hello]'


STRING('UPDATE: [',t.s,']')
'UPDATE: [World!]'

[VolkerBarth]

As Breck has pointed out:

> SQL Anywhere never trims or pads values with trailing blanks when the values are stored in the database.

That being said, you can certainly use triggers to trim character data before you store it, say in a BEFORE INSERT/UPDATE row-level trigger.