Getting a particular type string using regular expression in postgreSQL-CodePudding

I am trying to get a specific type to string from a jason column in the table using postgreSQL.

Here is the string snippet and from it I am trying to get rate and $ amount as each row (e.g. CommentFOS rate $00.00)

Last 4  CommentsFOS rate $10.00  OT/HOL $20.00,  AP rate $30.00  OT/HOL $40.00

This is the query I have currently

select id,  
       regexp_matches(current_data->>'description', '..\w\/\w* \$[0-9] [\.]*[0-9]','g') as rates
from table;

And the output I am getting is something like this

ID. rates
1   { OT/HOL $20.0}
1   { OT/HOL $40.0}

Also tried this query

select id, 
       regexp_matches(current_data->>'description', '\w*FOS \w* \$[0-9] [\.]*[0-9]','g') as rates
from table;

the output I am getting from above sql is

ID. rates
1   { CommentsFOS rate $10.00}

But, Output I am expecting like this

ID. rates
1   { CommentsFOS rate $10.00}   
1   { OT/HOL $20.00}
1   { AP rate $10.00}
1   { OT/HOL $40.00}

CodePudding user response：

You can use

\S (?:\s rate)?\s \$\d*\.?\d

See the