Skip to Content
avatar image
Former Member

Trim Trailing Spaces.

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 21, 2016 at 05:16 PM

    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!]'
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2016 at 07:26 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded