sqloracle-databaseregexp-substr

Extract value between two strings in SQL

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:

enter image description here

Solution

  • 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