How to get a substring value from a varchar in oracle based on specific pattern

Question

I have a column which has record in below specific format :-

ParamName1+ParamVal1;ParamName2+ParamVal2;ParamName3+ParamVal3;

Currently I am using below query logic but this logic also brings the value for which the part of the search ParamName matches :-

TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TO_CHAR(ITEM_ATRS_LIST),'ID\+[^;]*'),'ID\+',''))

For example if I have value EmpID+1234;Name+ABCD;Age+21; using query

TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TO_CHAR(ITEM_ATRS_LIST), 'ID\+[^;]*'),'ID\ + ',''))

gives us value 1234 but we want NULL since the ParamName ID is not present.

Do we have any way to bring value which matches completely with the search paramName ?

I'd use a programming language for this. PL/SQL comes to mind :-) — Thorsten Kettner
– Thorsten Kettner, Commented May 25, 2018 at 11:12
Do all those processing work in java,Don't pass this ugliness into database tables and sql code. :-) — Kaushik Nayak
– Kaushik Nayak, Commented May 25, 2018 at 11:23

Thorsten Kettner · Accepted Answer · 2018-05-25 12:01:54Z

You are looking for a value for an attribute, say for 'EmpID'. This is what I've come up with:

regexp_replace( regexp_substr(item_atrs_list, 
                              '(^|;)EmpID\+.*?(;|$)'
                             ),
                '^.*?\+(.*?)\;?$',
                '\1'
              )

Explantion for (^|;)EmpID\+.*?(;|$)':

(^|;) beginning of the string or a semicolon in the string
EmpID followed by EmpID
\+ followed by a plus sign
.*?(;|$)' maybe followed by some characters (the value), finally followed by a semicolon or hitting the end of the string

Explantion for '^.*?\+(.*?)\;?$':

^ the beginning of the string
^.*?\+ possibly followed by some characters, then a plus sign (i.e. ';EmpID+' or 'EmpID+')
(.*?)\;?$' possibly followed by some characters that we memorize (the value), then possibly a semicolon, then the end of the string

Explantion for \1:

The first (and only) string we memorized, i.e. the value

(I'm not that good with regular expressions. There may be a much easier way to achieve this. As mentioned I would rather use a programming language such as PL/SQL anyway.)

Gary_W · Accepted Answer · 2018-05-25 14:11:33Z

1

Here's another way to approach it using Common Table Expressions (CTE's). The first one, 'tbl', just sets up a source for the original data. 'params' creates a table of name-value pairs, looping using connect by and splitting on the separator of a semi-colon, then further by the plus sign. Then, just select from there where your name matches.

-- Original data
with tbl(data) as (
  select 'EmpID+1234;Name+ABCD;Age+21;' from dual
),
-- make a table of name-value pairs
params(name, value) as (
  select regexp_substr(data, '(^|;)(.*?)\+', 1, level, NULL, 2),
         regexp_substr(data, '\+(.*?)(;|$)', 1, level, NULL, 1)
  from tbl
  connect by level <= regexp_count(data, ';')
)
--select * from params;
-- Get the value for the name
select name, value
from params
where name = 'EmpID';

answered May 25, 2018 at 14:11

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

2 Comments

Andrew Over a year ago

I think this is a much nicer generic solution

Gary_W Over a year ago

Just another way to approach the problem. Turning the name-value pairs into a table may give more flexibility on working with the data but maybe at the expense of some performance if the data set could be large. As usual one should test with one's own data and needs and weigh the pro's and cons. Information is power!

Collectives™ on Stack Overflow

How to get a substring value from a varchar in oracle based on specific pattern

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related