Question

Tokenizing Strings in Oracle

  • 9 August 2023
  • 1 reply
  • 21 views
haritha.waidyaratne
Rank
Badge +4

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
Rank
Userlevel 7
Badge +21

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

Reply


Terms & ConditionsCookie settings