Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    32

    Unanswered: Trim function - on all fields

    Hello

    I have a table to table copy exercise to do. Thus my script is general of the type select * from table_name. This general script is called by providing table_name as a parameter. I need to ensure that all the fields are trimmed, i.e. leading and trailing white spaces eliminated.

    How can I ensure this?

    The fields I'm calling vary from table to table and thus I can't specify this in my query.

    Thanks folks

    S. BASU

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Short answer: this is impossible -- you have to write INSERT statements which contain SELECT clauses with TRIM around each column name, so you need all column names.
    On the other hand, your cold of course *generate* that SQL statement to do the insert from the DB2 catalog, by querying syscat.columns or sysibm.syscolumns.
    Since this will return multiple rows, the easiest way to combine this into a single (dynamic) SQL statement is to use a cursor in your stored procedure; in the loop, concatenate
    ', trim(' || colname || ')'
    to an initially empty variable (varchar); after the loop, removed the leading ',' and concat it into the full INSERT ... statement.
    Then prepare & execute the dynamic SQL statement.

    (Writing out the details is left as an exercise :-)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2012
    Posts
    32
    Peter

    Thanks for that. however I've partly understood your solution:

    Quote Originally Posted by Peter.Vanroose View Post
    Short answer: this is impossible -- you have to write INSERT statements which contain SELECT clauses with TRIM around each column name, so you need all column names.
    On the other hand, your cold of course *generate* that SQL statement to do the insert from the DB2 catalog, by querying syscat.columns or sysibm.syscolumns.
    Gotcha upto here.

    Quote Originally Posted by Peter.Vanroose View Post
    Since this will return multiple rows, the easiest way to combine this into a single (dynamic) SQL statement is to use a cursor in your stored procedure; in the loop, concatenate
    ', trim(' || colname || ')'
    to an initially empty variable (varchar); after the loop, removed the leading ',' and concat it into the full INSERT ... statement.
    Then prepare & execute the dynamic SQL statement.

    (Writing out the details is left as an exercise :-)
    Lost

    I know that you've given me an exercise so i wouldn't want to cheat but SQL is not really my forte and especially DB2 is something I started working with since April this very year.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Actually, this is not so much a DB2 specific issue, rather standard SQL PL ;-)
    Anyhow, here are a few more details (without any guarantee, have not tried them out).
    Most likely some fine-tuning will be needed...
    Code:
      DECLARE n VARCHAR(32); -- column name
      DECLARE t VARCHAR(10); -- column type
      DECLARE s VARCHAR(4000) DEFAULT ''; -- the statement to be generated
      DECLARE c CURSOR FOR
        SELECT colname, typename
        FROM   syscat.columns
        WHERE  tabname = p_tablename AND tabschema = p_schema
          AND  hidden = ' '
        ORDER BY colno;
    
      OPEN c;
      FETCH c INTO n, t;
      WHILE SQLCODE = 0 DO
        IF t IN ('CHARACTER','VARCHAR','CLOB') THEN
          SET s = s || ', trim(' || n || ') AS ' || n;
        ELSE
          SET s = s || ', ' || n;
        END IF;
        FETCH c INTO n, t;
      END WHILE;
      IF s = '' THEN
        SET s = 'The table named '||p_schema||'.'||p_tablename||' does not exist.';
      ELSE
        SET s = 'INSERT INTO other_table SELECT ' || substr(s, 2) || ' FROM ' || p_tablename;
      END IF;
    (The "p_*" variables are the passed in parameters.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by Peter.Vanroose View Post
    Actually, this is not so much a DB2 specific issue, rather standard SQL PL ;-)
    Anyhow, here are a few more details (without any guarantee, have not tried them out).
    Most likely some fine-tuning will be needed...
    Code:
      DECLARE n VARCHAR(32); -- column name
      DECLARE t VARCHAR(10); -- column type
      DECLARE s VARCHAR(4000) DEFAULT ''; -- the statement to be generated
      DECLARE c CURSOR FOR
        SELECT colname, typename
        FROM   syscat.columns
        WHERE  tabname = p_tablename AND tabschema = p_schema
          AND  hidden = ' '
        ORDER BY colno;
    
      OPEN c;
      FETCH c INTO n, t;
      WHILE SQLCODE = 0 DO
        IF t IN ('CHARACTER','VARCHAR','CLOB') THEN
          SET s = s || ', trim(' || n || ') AS ' || n;
        ELSE
          SET s = s || ', ' || n;
        END IF;
        FETCH c INTO n, t;
      END WHILE;
      IF s = '' THEN
        SET s = 'The table named '||p_schema||'.'||p_tablename||' does not exist.';
      ELSE
        SET s = 'INSERT INTO other_table SELECT ' || substr(s, 2) || ' FROM ' || p_tablename;
      END IF;
    (The "p_*" variables are the passed in parameters.)
    Thanks immensely for that. Will try it out and see what I get.

    Much appreciated Peter.
    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •