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

Question

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.

Gordon Linoff · Accepted Answer · 2014-11-23 13:27:04Z

2

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)

answered Nov 23, 2014 at 13:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DoomerDGR8 Over a year ago

This single line doesn't even need and extra ',' in the end to ensure proper work. It just works perfectly.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related