Extract Specific Parameter value using Regex Postgresql

Question

Given input string as

'PARAM_1=TRUE,THRESHOLDLIST=kWh,2000,Gallons,1000,litre,3000,PARAM_2=TRUE,PARAM_3=abc,123,kWh,800,Gallons,500'

and unit_param = 'Gallons' I need to extract value of unit_param (Gallons) which is 1000 using postgresql regex functions.

As of now, I have a function that first extracts value for THRESHOLDLIST which is "kWh,2000,Gallons,1000,litre,3000", then splits and loops over the array to get the value.

Can I get this efficiently using regex.

SELECT substring('PARAM_1=TRUE,THRESHOLDLIST=kWh,2000,Gallons,1000,litre,3000,PARAM_2=TRUE,PARAM_3=abc,123,xyz' FROM '%THRESHOLDLIST=#".........#",%' FOR '#')

I assume switching that to a better (parsable) format (e.g. JSON) isn't an option? That would make things a lot easier — user330315
– user330315, Commented Jan 28, 2021 at 15:41

Bohemian · Accepted Answer · 2021-01-28 20:03:00Z

1

Use substring() with the target input grouped:

substring(myCol, 'THRESHOLDLIST=[^=]*Gallons,([0-9]+)')

The expression [^=]* means “characters that are not =”, so it won’t match Gallons within another parameter.

edited Jan 28, 2021 at 20:03

answered Jan 28, 2021 at 15:35

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Furqan Shaikh Over a year ago

the occurrence of Gallon could be in other parameters too. I want to confirm the value is coming from the THRESHOLDLIST.

Bohemian Over a year ago

@FurqanShaikh better update your question to say that then, plus update your example to include two occurrences of Gallon to make it clear.

Bohemian Over a year ago

@furq I’ve updated the regex to only match within THRESHOLDLIST

Kalina Mielke · Accepted Answer · 2021-01-28 15:40:11Z

0

select Substring('PARAM_1=TRUE,THRESHOLDLIST=kWh,2000,Gallons,1000,litre,3000,PARAM_2=TRUE,PARAM_3=abc,123,xyz' from 'Gallons,\d*');

returns Gallons,1000

answered Jan 28, 2021 at 15:40

Kalina Mielke

1115 bronze badges

Collectives™ on Stack Overflow

Extract Specific Parameter value using Regex Postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related