Extract Value between Two Strings in SQL

We can use Oracle's REGEXP_REPLACE function to extract the value between two strings. For instance, to extract the price from the string "monday12monday", we can use the following code:

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

This will return the value "12".

We can also use the REGEXP_SUBSTR function to extract the value between two strings. For instance, to extract the prices from the string "monday12monday, tuesday567tuesday, friday87friday", we can use the following code:

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
    

This will return the following result:

MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY
12	567	NULL	NULL	87

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