CODEWITHSUNDEEP
sqlregexoracle-database
satya
satya

Reputation: 13

Regular Expression in oracle 11g how to retrieve data from a column

I have the following data stored in the database column xyz:

a=222223333;b=433333657675457;c=77777;
a=52424252424242;d=5353535353;b=7373737373;

There is no requirement that b value should always be there but if b value is present I have to retrieve the value following b=.

I want to retrieve the value of b using regular expressions in Oracle and I am unable to do it.

Can anyone please help me find a solution for this?

Upvotes: 0

Views: 130

Answers (4)

Mohd Ahmad
Mohd Ahmad

Reputation: 55

enter image description hereabove solution is working in all the cases even if b contains alphanumeric

Upvotes: -2

Patrick Bacon
Patrick Bacon

Reputation: 4660

Use a Subexpression to Select a Substring of Your REGEXP_SUBSTR Matching Pattern

My match pattern, 'b=(\d+);', includes the parenthesis which mark this subexpression which is the last parameter of REGEXP_SUBSTR.

If you look at the 12c documentation, you will see that the third example uses a subexpression.

The escaped d just is a regular expression shorthand to indicate that we are looking for digits and the plus symbol is a quantifier indicating 1 or more digits.

SCOTT@db>WITH smple AS (
  2      SELECT
  3          'a=52424252424242;d=5353535353;b=7373737373;' dta
  4      FROM
  5          dual
  6  ) SELECT
  7      dta,
  8      regexp_substr(a.dta,'b=(\d+);',1,1,NULL,1) subexp
  9    FROM
 10      smple a;
DTA                                           subexp
---------------------------------------------------------
a=52424252424242;d=5353535353;b=7373737373;   7373737373

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can use regexp_substr:

select substr(regexp_substr(';' || xyz, ';b=\d+'), 4) from your_table;

Concatenation with ; is to distinguish between key-value pair with key say 'ab' and 'b'.

Upvotes: 1

Abra
Abra

Reputation: 20924

I suggest using Oracle built-in function REGEXP_SUBSTR which returns a substring using regular expressions. According to the example you posted, the following should work.

SELECT REGEXP_SUBSTR(xyz, 'b=\d+;') FROM your_table

Upvotes: 1

Related Questions