Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: substr function shows out of range

    can someone let me knwo what's wrong with the belwo substring function

    Code:
    db2 "select length('employee,employee-desc')  from sysibm.sysdummy1"
    
    1
    -----------
             22
    
      1 record(s) selected.
    
    db2 "select substr('employee,employee-desc',1,22) from sysibm.sysdummy1"
    
    1
    ----------------------
    employee,employee-desc
    
      1 record(s) selected.
    
    db2 "select substr('employee,employee-desc',10,22) from sysibm.sysdummy1"
    SQL0138N  A numeric argument of a built-in string function is out of range.
    SQLSTATE=22011
    Why do it says it's out of range ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The second numeric argument is number of bytes, not ending position. So you probably want this:

    db2 "select substr('employee,employee-desc',10,13) from sysibm.sysdummy1"
    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
    Mar 2010
    Posts
    32
    Thank you .i understood my mistake

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    db2 "select substr('employee,employee-desc',10) from sysibm.sysdummy1"
    Do not use the 3rd argument and you'll get a string from position in 2nd argument to end of the string.

    Lenny

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Can we use left or right function instead ?

    I use RIGHT or LEFT functions mostly. Can this be better or worse approach ?

    Code:
    [dbfinder@centos ~]$ db2 "select right('employee,employee-desc',12) from sysibm.sysdummy1"
    
    1
    ----------------------
    mployee-desc
    
      1 record(s) selected.
    Regards,

    DBFinder

Posting Permissions

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