CODEWITHSUNDEEP
sqloracle-database
Nawed Shaikh
Nawed Shaikh

Reputation: 419

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

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 ?

Upvotes: 0

Views: 166

Answers (2)

Gary_W
Gary_W

Reputation: 10360

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';

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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.)

Upvotes: 1

Related Questions