Extract value between two strings in SQL

Question

I'm trying to extract price for each day from a tag list in SQL Developer.

This is my list (I can use another list format if needed):

monday12monday, tuesday567.12tuesday, friday87friday

I'm looking for this result :

tuesday
567.12

monday
12

friday
87

How can I get the value between two strings using "REGEXP_SUBSTR"?

I tried this, but it doesn't work:

Answer 1

I'm not entirely sure how you want to have your data returned, but this should get you on the right track. The final parameter '1' in REGEXP_SUBSTR fetches the first capturing group (\d+), which is the number that comes after the day's name.

WITH tab AS (
  SELECT 'monday12monday, tuesday567tuesday, friday87friday' AS tags
  FROM DUAL
  )
SELECT 
  REGEXP_SUBSTR(tags, 'monday(\d+)', 1, 1, NULL, 1) AS monday, 
  REGEXP_SUBSTR(tags, 'tuesday(\d+)', 1, 1, NULL, 1) AS tuesday, 
  REGEXP_SUBSTR(tags, 'wednesday(\d+)', 1, 1, NULL, 1) AS wednesday, 
  REGEXP_SUBSTR(tags, 'thursday(\d+)', 1, 1, NULL, 1) AS thursday, 
  REGEXP_SUBSTR(tags, 'friday(\d+)', 1, 1, NULL, 1) AS friday
FROM tab

Returns:

MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY
12	567	null	null	87

db<>fiddle here

Edit: If there are decimals or thousand signs in the numbers you need to deal with, change the capture group pattern from (\d+) to (\d+[\.,]?\d*[\.,]?\d*)

db<>fiddle here

Answer 2

In Oracle you can use REGEXP_REPLACE. If you are needing the number which is the Price as you say pulled out of your String you can Replace all non numbers with a blank and simply get back the Number from within the String using the below.

REGEXP_REPLACE('monday12monday', '\D', '')

Returns 12

---

Update (Generic Pattern for ALL Days): I no longer have access to an Oracle DBMS however rather than replacing using REGEXP_REPLACE as I show above you can Match on All of the Groups and capture each group that is returned using a single RegEx Pattern.

Pattern (?:sunday|monday|tuesday|wednesday|thursday|friday|saturday)(\d+)

Example for Original Sting (No decimals) on RegEx101.com: https://regex101.com/r/0lb6Gu/1

Screenshot of Captured Groups using Original Pattern:

---

This generic pattern will find all the days in the week and return the Captured Group(s) all in one go.

Pattern (?:sunday|monday|tuesday|wednesday|thursday|friday|saturday)(\d+[\.,]?\d*[\.,]?\d*)

Example of RegEx Pattern matching on numbers that have decimals on RegEx101.com: https://regex101.com/r/23jP7i/1

Screenshot of Captured Groups using updated Pattern for decimals:

Answer 3

How about this method that uses CONNECT BY to parse the string by it's delimiter of "comma-space", then for each element found splits that into it's day and price components? This way it will work no matter how many elements are in the string.

Caveat: There is no checking that day names are correct.

column day format a15
column price format a6

with tbl(str) as (
  select 'monday12monday, tuesday567.12tuesday, friday87friday' from dual
)
select regexp_substr(str, '(\D+)\d.*?(, |$)', 1, level, null, 1) day,
  regexp_substr(str, '\D+(\d+[.]?\d*)\D+(, |$)', 1, level, null, 1) price
from tbl
connect by level <= regexp_count(str, ', ') + 1;


DAY             PRICE 
--------------- ------
monday          12    
tuesday         567.12
friday          87    

3 rows selected.