Skip to main content
Question

Tokenizing Strings in Oracle

  • August 9, 2023
  • 1 reply
  • 53 views
haritha.waidyaratne
Sidekick
Forum|alt.badge.img+5

Hi,

Any clue how this could be done in an easier way?

SELECT SUBSTR(string_x_,                                                                    -- 1st param for SUBSTR
                                (INSTR(string_x_, 'IDENTITY') + 9),                        -- 2nd param for SUBSTR
                                (INSTR(string_x_, '^', INSTR(string_x_, 'IDENTITY')) -          --3rd param for
                                 (INSTR(string_x_, 'IDENTITY') + 9)                                            -- SUBSTR
                                )
                              )
FROM DUAL;

Note: I want to find the value for IDENTITY in string_x_ = 'IDENTITY=IFSMIG^ROLE=MSS_RECRUITMENT_APPROVAL_LTU^'

    1 reply

    dsj
    Ultimate Hero (Partner)
    Forum|alt.badge.img+22
    • dsj
    • Ultimate Hero (Partner)
    • 905 replies
    • August 15, 2023

    Hi @haritha.waidyaratne 

    You can use Client_SYS.Get_Key_Reference_Value to get the values from a keyref formatted string

     

    Eg:

    declare 
      string_              VARCHAR2(2000) := 'IDENTITY=IFSMIG^ROLE=MSS_RECRUITMENT_APPROVAL_LTU^';
      identity_            VARCHAR2(2000);
      role_                VARCHAR2(2000);
    begin
      identity_ := Client_SYS.Get_Key_Reference_Value(string_, 'IDENTITY');
      role_ := Client_SYS.Get_Key_Reference_Value(string_, 'ROLE');

      DBMS_OUTPUT.put_line(identity_);
      DBMS_OUTPUT.put_line(role_);

    end;

     

    Cheers!

    Damith

    DSJ - https://dsj23.me


    Terms & ConditionsCookie settingsAccessibility statement