CODEWITHSUNDEEP
sqloracle-databasesubstring
Brian Brock
Brian Brock

Reputation: 351

Extracting a value from a key/value pair stored in a text field

I need to extract the value from a key/value pair stored in a text field using sql in oracle 11g.

I can detect the "key" with 

    SELECT *
    FROM mytable
    WHERE valuet2 LIKE '%' || chr(10) || 'F;' || '%'

but I'm not confidant this is the best way to do the search, and I don't know how to return the value of variable length (up to, but not including the carriage return).

This is the text field I need to search against and extract the value from.

;Please Select;*
E;Expelled
F;Expelled Following Suspension
N;In-School Suspension
S;Out-of-School Suspension
BS;Bus Suspension
101;Detention
130;Conference / Warning
131;Parent Contact / Conference
200;Loss of Recess

I'm querying a separate table that stores the "key", so I need to do the lookup from this text field to determine what that key value represents. I will be pushing this query out to other servers that will have their own unique combinations of key/value pairs, and I cannot anticipate what those may be. Therefore, I cannot write a decode.

Upvotes: 0

Views: 931

Answers (2)

user5683823
user5683823

Reputation:

You can use regular expression functions and take advantage of the 'm' modifier, which instructs Oracle to treat ^ and $ as the start-of-line and end-of-line anchors (rather than matching only at the beginning and the end of the string). Something like this:

select regexp_substr(valuet2, '^F;(.*)$', 1, 1, 'm', 1)
from   mytable
where  regexp_like(valuet2, '^F;', 'm')
;

Brief demo:

create table mytable (valuet2 varchar2(4000));

insert into mytable(valuet2) values(
';Please Select;*
E;Expelled
F;Expelled Following Suspension
N;In-School Suspension
S;Out-of-School Suspension
BS;Bus Suspension
101;Detention
130;Conference / Warning
131;Parent Contact / Conference
200;Loss of Recess'
);

select regexp_substr(valuet2, '^F;(.*)$', 1, 1, 'm', 1) as myval
from   mytable
where  regexp_like(valuet2, '^F;', 'm')
;

MYVAL                                   
----------------------------------------
Expelled Following Suspension

Here F is hardcoded, but you can replace it with a bind variable; the query needs to be tweaked slightly. Please write back if you need help with that.

Upvotes: 1

Stephen Caggiano
Stephen Caggiano

Reputation: 166

My not 100% sure what you are looking for but

select substr(valuet2,length(:val)+2) FROM mytable WHERE valuet2 LIKE :val || ';%';

will work to get everything after the ; where the before matches :val

Upvotes: 0

Related Questions