CODEWITHSUNDEEP
oracle-databaseplsql
DoomerDGR8
DoomerDGR8

Reputation: 5042

Find string value based on a string key in a given string

I have a string like so:

Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1

I'm writing a function that will receive this value as parameter and another parameter as UserName

I need to find the value against key UserName from the given string which is admin

I'm searching if there is a RegEx within oracle to help out here.

Here is what I have made so far:

CREATE OR REPLACE FUNCTION GET_CSV_FIELD(Parameter_RowData IN CLOB, Parameter_Field_Name IN VARCHAR2 )
  RETURN VARCHAR2
AS
  Found_Index INTEGER;
  End_Index INTEGER;
  Pair_Index INTEGER;

  Return_Result VARCHAR2(4000); 

BEGIN

   Found_Index := INSTR(Parameter_RowData, Parameter_Field_Name);

   IF Found_Index > 0 THEN
    End_Index := INSTR(Parameter_RowData, ',', Found_Index);
    Pair_Index := INSTR(Parameter_RowData, '=', Found_Index);

    IF End_Index = 0 THEN
      Return_Result := '';
      RETURN Return_Result;
    END IF;

    IF Pair_Index = 0 THEN
      Return_Result := '';
      RETURN Return_Result;
    END IF;   

    Return_Result := SUBSTR(Parameter_RowData, Pair_Index + 1, End_Index - Pair_Index - 1);
   ELSE
    Return_Result := '';    
   END IF;

RETURN Return_Result;
END;

Any better method? Thanks.

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this with regular expressions:

select substr(regexp_substr(str, 'UserName=[^,]+'), 10)

The general method would be

select substr(regexp_substr(str, v_param || '=[^,]+'), length(v_param) + 2)

Upvotes: 2

Related Questions