Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: Select query - extracting numbers from varchar column

    DB2 9.7 /Win 2008

    I need to extract 5 digit number from varchar(256) column.
    The 5 digit number can be found in any position.

    eg : Please extract 10987 five digit number
    eg: 30934 is the number should be extracted

    Can you please guide me to wring a SELECT query?

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Extract first 5 digit number.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data
    ( id , vchar_col ) AS (
    VALUES
      ( 1 , 'Please extract 10987 five digit number'   )
    , ( 2 , '30934 is the number should be extracted'  )
    , ( 3 , 'Are 01234 and 56789 both 5 digit number?' ) 
    )
    SELECT id
         , SUBSTR(
              vchar_col
            , POSSTR( TRANSLATE(vchar_col , '' , '123456789' , '0') , '00000' )
            , 5
           ) AS extracted
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    ID          EXTRACTED
    ----------- ---------
              1 10987    
              2 30934    
              3 01234    
    
      3 record(s) selected.

  3. #3
    Join Date
    Nov 2011
    Posts
    87

    Thanks

    Great, it works.

    Thanks tonkuma.
    may i know why 56789 was not extracted? i cant work it out.








    db2mtrk

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    POSSTR find first occurence of second parameter in the first parameter.
    POSSTR - IBM DB2 9.7 for Linux, UNIX, and Windows

    If you want to find second or subsequent occurence,
    you may want to use INSTR(a sysnonym for LOCATE_IN_STRING) function.
    INSTR - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

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