I’m encountering an issue with my SQL query. My goal is to extract the second match from a string that contains percentages. Here is the query I’m using:
SELECT (regexp_matches('Main fabric: 50% Organic Cotton, 49% Cotton, 1% Elastane', '\b(\d+)%'))[2];
Unfortunately, this returns no results. However, when I test the regex on an online tool, it successfully finds three matches. How can I adjust my query to obtain the second match? I am working with Postgres 16.
Have you tried using the regexp_match function combined with an array or subquery? PostgreSQL’s functions can be a bit tricky. What if you first store all matches and then select the one you want? I’m curious about how flexible the database is with regex. Let us know what you try next!
you might need a different approach, try using regexp_split_to_array to split the string by comma and space, then select the second element from the array. give it a shot and see if you get the desired result that way.