Search code examples
sqlregexoracle-databaseregexp-replace

Oracle Regex grabbing value from key value pair in a string

Consider the following column row:

col
-------------------------
'{"day":"8","every":"2"}'

I am trying to get 8 from this string using regular expression to figure out the day.

so far I have: 

SELECT 
    regexp_replace(col, '{"day":[^0-9]', '') as "day"
FROM 
   mytable;

This gives me: 

 day
 ---------------
 8","every":"2"}

I am having trouble figuring out how to filter out the rest of the string from the first number forward. In my example I just want the number 8 for this row.

Solution

  • How about this?

    SELECT 
    regexp_replace(col, '{"day":"([0-9]+).*', '\1') as "day"
FROM 
   mytable;