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

Postgresql – Replace Nth group of characters in a string split by underscores _

I want to splits a string on '_' and replace 3rd group with 'oooo'.

Is there any way to do it simply and without assuming there’s exactly 5 groups?

CONCAT( SPLIT_PART(test, '_', 1)
       ,SPLIT_PART(test, '_', 2)
       ,'OOOO'
       ,SPLIT_PART(test, '_', 4)
       ,SPLIT_PART(test, '_', 5)
      )

Example:
'GjHf00_i8S7b_tst00_UL000_perce'
turns into this
'GjHf00_i8S7b_oooo_UL000_perce'

Tags:

3

Answers


  1. 0

    demo at db<>fiddle

    select regexp_replace(test,'[^_]+','oooo',1,3)
from examples;

    It’s looking for substrings of 1 or more non-underscores, starting from position 1 and trying to replace the 3rd hit:

    • [] is a group of allowed characters
    • ^ inside a group means it’s a group you wish to exclude
    • _ is just the underscore
    • + means you’re looking for 1 or more of that

    Where to start and which match to replace is handled by the rest of regexp_replace() params:

    regexp_replace ( string text, pattern text, replacement text, start integer, N integer [, flags text ] ) → text
    Replaces the substring that is the N’th match to the POSIX regular expression pattern, or all such matches if N is zero; see Section 9.7.3.

    If you’re fine with the assumption that you always have 5 groups, your idea would work as long as you swap out concat() for concat_ws(). Otherwise you’d lose the underscores you’re splitting on:

    select concat_ws('_',split_part(test, '_', 1)
                    ,split_part(test, '_', 2)
                    ,'oooo'
                    ,split_part(test, '_', 4)
                    ,split_part(test, '_', 5) )
from examples;
    Login or Signup to reply.
  2. 0

    You could also use a regular expression with back references, like:

    regexp_replace(str, '(^[^_]*_[^_]*_)[^_]+(.*$)?','1oooo2')

    Typically not faster.

    fiddle

    Login or Signup to reply.
  3. 0

    Try matching:

    ^((?:[^_]+_){2})([^_]+)

    and replacing with:

    1oooo

    See: regex101 and fiddle

    Explanation

    MATCH:

    • ^: start of string
    • ((?:[^_]+_){2}): Capture the first two blocks to group 1
    • ([^_]+): Capture the third block to group 2

    REPLACE:

    • 1: Keep the start
    • oooo: Replace third block with "oooo"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search