Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: SUBSTR - need to calc the start position...

    Hey,

    I'm writing queries against DB2 v8.1.0.36.
    db2level returns 'Instance "DB2" uses "32" bits and DB2 code release "SQL08010" with level identifier "01010106".

    I have a 60 character column called "Class".
    The values in "Class" are Basic, Advanced, Basic-EXT, Advanced-EXT.
    I need to identify only those values that end with "-EXT".

    I had thought that I could use SUBSTR but I'm not providing the correct syntax to identify the start position which needs to the the length - 2.

    SUBSTR (Class, LENGTH(RTRIM(Class))-2,2)

    It does process SUBSTR (Class, LENGTH(RTRIM(Class)),2) but I only get 'T' back.

    Any ideas?

    Many Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are computing the length of the string after any trailing blanks have been removed, but you are not removing them from the base string. Try something like: substr(rtrim(class),length(rtrim(class))-2,2)

    Andy

  3. #3
    Join Date
    Dec 2008
    Posts
    76
    If you only need to identify them why not use LIKE '%-EXT'
    RD

  4. #4
    Join Date
    Aug 2009
    Posts
    2

    Done and DONE

    Thanks to both of you for your advice. The LIKE '%-EXT%' got me what I needed.

    Preesh.....

Posting Permissions

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