Skip to Content
0

Trim Trailing Spaces.

Oct 20, 2016 at 11:47 PM

119

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Breck Carter Oct 21, 2016 at 05:16 PM
0

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!]'


Share
10 |10000 characters needed characters left characters exceeded
Volker Barth Oct 24, 2016 at 07:26 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded