Regex capture first words before character subsequently - Postgresql

user_5737654 0
Viewed 86

With refference to this question but now in postgress How do i capture the word before the = sign, there is another equal sign in the URL which i do not want to capture

SELECT
  TRIM(',' FROM REGEXP_REPLACE('TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3',
                '=[^|]+($|\|)', ',')) "PARAMS"

From the above string TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3

I only want TType,Status,URL,day

Note: The string has a pipe delimiter | for the parameters

postgresql regex
2 Answers

You can use

SELECT ARRAY_TO_STRING (
 ARRAY (
   SELECT REGEXP_MATCHES (
      'TType=SEND|Status=OK|URL=min://j?_a=3&ver=1.1|day=3',
      '(?:\||^)(\w+)=', 'g'
   )
 ), ',') as PARAMS

See the regex demo.

NOTE: REGEXP_MATCHES returns only capturing group value if a capturing group is defined in the regex pattern. Here, the pattern means

  • (?:\||^) - (a non-capturing group matching either) | or start of string
  • (\w+) - Capturing group 1 (the actual return value): any one or more alphanumeric chars
  • = - a = char.

The REGEXP_MATCHES result is case to an array first and then conveted to a string with ARRAY_TO_STRING.

user_38329700

step-by-step demo:db<>fiddle

SELECT
    string_agg(                                          -- 3
        split_part(elements, '=', 1),                    -- 2        
        ','
    )
FROM mytable,
    regexp_split_to_table(mystring, '\|') as elements    -- 1
  1. Split the string into params like A=B. Move every into a separate record
  2. Split these elements at the = character and return the first split
  3. Finally aggregate all these first splits to a string list.
user_39842210
Related