Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    17

    Unanswered: DB2 leading zeros Trim/truncating

    DB2 Ver9.1 on Windows o/s.

    i have a column which stores member id's... in the database, values are stored as 0000088811, but on the front end app, the value for member id is only 5 digits(which may have zero - ex: 01234 as member id). question is of the 10 digits stored for the column, i want to get last five digits and want to truncate/Trim the leading 5 zeros.
    my query: select STRIP(col_name, L, '0') as alias_name from table_name followed by rest of the query..... - but the problem is its striping all leading zeros, but my 5-digit member id field can have zero as its first digit...
    which function and whats the syntax to my issue?
    Any help is greatly appreciated...
    Note: I'm very new to DB2.....

    Cheers
    Han

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Use the substring function:

    substr(col_name,6,5)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2010
    Posts
    17

    Thumbs up It works!

    Thanks much, it works with a minor change substr(col_name,5,5)

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hmm. If the column is ten digits and you only want the last five, wouldn't that be digits 6-10 (start at 6, lenght of 5)?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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