PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Adding a comma to strings that matches a regular expression pattern, but a few characters away

I am trying to add a comma and a space to my strings. Here are some examples:

{ELEPHANT:1, FENNEC_FOX:1NAKED_MOLE:1URCHIN:2}

{DUNG_BEETLE:12URCHIN:1}

{DUNG_BEETLE:1FENNEC_FOX:1URCHIN:2}

Notice the inconsistent lack of ", ". I would like to outcome to be

{ELEPHANT:1, FENNEC_FOX:1, NAKED_MOLE:1, URCHIN:2}

{DUNG_BEETLE:1, URCHIN:1}

{DUNG_BEETLE:1, FENNEC_FOX:1, URCHIN:2}

I think I need to use REGEXP_REPLACE(my_string, ':[0-9]+[a-z_A-Z]', replacement). But I’m not quite sure how to make replacement be the colon and whatever the number is, a comma and a space, and whatever the matching letter is.

Tags:

2

Answers


  1. 0

    This expression gets your desired result (fastest in a quick test with 100k rows):

    regexp_replace(my_string, '(:d+)(?=[a-zA-Z])', '1, ', 'g')

    Core features are the positive lookahead (?=[a-zA-Z]) and the 4th parameter 'g'.
    Working with a positive lookbehind is slower for this – as hinted by Nick.

    regexp_replace(my_string, '(?<=:d+)([a-zA-Z])', ', 1', 'g')

    fiddle

    Related answer with more explanation:

    Login or Signup to reply.
  2. 0

    Your regex is fine, although you can simplify it by using d in place of [0-9] and w in place of [a-z_A-Z]. You then need to use capturing groups to save the matched text and insert it into the replacement string:

    SELECT REGEXP_REPLACE(my_string, '(:d+)(w)', '1, 2', 'g')
FROM my_table

    Output:

    {ELEPHANT:1, FENNEC_FOX:1, NAKED_MOLE:1, URCHIN:2}
{DUNG_BEETLE:12, URCHIN:1}
{DUNG_BEETLE:1, FENNEC_FOX:1, URCHIN:2}

    If you’re trying to convert this into valid JSON, you’ll need a second step to enclose the keys in double quotes:

    SELECT REGEXP_REPLACE(REGEXP_REPLACE(my_string, '(:d+)(w)', '1, 2', 'g'), '(w+):', '"1":', 'g')
FROM my_table

    Output:

    {"ELEPHANT":1, "FENNEC_FOX":1, "NAKED_MOLE":1, "URCHIN":2}
{"DUNG_BEETLE":12, "URCHIN":1}
{"DUNG_BEETLE":1, "FENNEC_FOX":1, "URCHIN":2}

    Demo on dbfiddle.uk

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search