Results 1 to 6 of 6

Thread: Substr Instr

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Substr Instr

    Hi guys

    I need to extract the value of OFFICEDIR from the following 2 strings....

    NOTE: The position of OFFICEDIR is changed in both strings.

    Code:
    TCC-MSS01/Incoming Office:LABEL=MSS02 OFFICEDIR=13/Calling Number:ALL/Outgoing Office:ALL/Called Number:ALL/CallType:ALL
    Code:
    TCC-MSS01/Incoming Office:ALL/Calling Number:ALL/Outgoing Office:LABEL=CRBT2 OFFICEDIR=5/Called Number:1236/CallType:ALL
    Can one SUBSTR INSTR statement do the trick?

    Regards

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The value which you want to exract may be the sring between 'OFFICEDIR=' and '/'.

    If so,
    I thought that it might be easy to extract the value by using one SUBSTR function and two or three INSTR functions.


    If you want a way to extract the sring by using one SUBSTR function and one INSTR function,
    it must a challenge.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks.

    As long as it's one statement, it can be any number of INSTR functions.....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Does it have to be SUBSTR and INSTR? How about regular expressions, such as
    Code:
    SQL> WITH test
      2          AS (SELECT 'TCC-MSS01/Incoming Office:LABEL=MSS02 OFFICEDIR=13/Calling Number:ALL/Outgoing Office:ALL/Called Number:ALL/CallType:ALL'
      3                        col
      4                FROM DUAL
      5              UNION ALL
      6              SELECT 'TCC-MSS01/Incoming Office:ALL/Calling Number:ALL/Outgoing Office:LABEL=CRBT2 OFFICEDIR=5/Called Number:1236/CallType:ALL'
      7                        col
      8                FROM DUAL)
      9  SELECT LTRIM (REGEXP_SUBSTR (col, 'OFFICEDIR=\d+'), 'OFFICEDIR=') result
     10    FROM test;
    
    RESULT
    --------------------------------------------------------------------------------
    13
    5
    
    SQL>

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Yes Reg Expr will work.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Thanks a lot my friend.

Posting Permissions

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