Results 1 to 3 of 3

Thread: substr issue

  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Unanswered: substr issue

    Hi All,

    i got stuck in a problem

    here it is:am using DB2:
    data in the datbase is like :
    Column name : Part_NO:
    Data: 6V8397
    7J9108
    5K9090

    My need is to insert '-' at third position
    the output should be : 6V-8397
    7J-9108
    5K-9090

    Now i wrote quert like this , Select distinct SUBSTR(A.Part_NO, 1, 3) || '-' || SUBSTR(A.Part_NO, 4, 4) as "Part Number" from Z1GD001$.HOSE_CPLNG_DET A ;

    The query is working but for same data its displaying like "7J9-108", am not sure why it happends.

    can anyone help me out
    thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see manuals to konw what are the parameters of the function.

    IBM Knowledge Center

    Code:
    >>-SUBSTR--(--string--,--start--+-----------+--)---------------><
                                    '-,--length-'
    The schema is SYSIBM.

    The SUBSTR function returns a substring of a string.

    ...
    ...

    start
    An expression that specifies the position of the first byte of the result for a character string or a binary string or the position of the first character of the result for a graphic string. The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The integer value must be between 1 and the length or maximum length of string, depending on whether string is fixed-length or varying-length (SQLSTATE 22011, if out of range). It must be specified as number of bytes in the context of the database code page and not the application code page.

    length
    An expression that specifies the length of the result. If specified, the expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The value of the integer must be in the range of 0 to n, where n equals (the length attribute of string) - start + 1 (SQLSTATE 22011, if out of range).
    ...
    ...
    For example:
    Code:
    data    : 7J9108
    position: 123456
    SUBSTR(A.Part_NO, 1, 3) means "start from position 1 length 3": 7j9
    SUBSTR(A.Part_NO, 4, 4) means "start from position 4 length 4": 108

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My need is to insert '-' at third position
    the output should be : 6V-8397
    7J-9108
    5K-9090
    You may want to use INSERT scalar-function.
    IBM Knowledge Center

    For example: INSERT(Part_NO , 3 , 0 , '-')

Posting Permissions

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