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:
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 |
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*)
\d+[\.,]?\d*[\.,]?\d*
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:
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.
