CODEWITHSUNDEEP
regexpostgresql
rshar
rshar

Reputation: 1475

Concatenate special characters to the column values based on pattern matching in Postgres

I have a table with following column in postgres

col1
C29[40
D1305_D1306delinsKK
E602C[20

I would like to append a string 'p.' & closing square brackets in row 1 and 3 elements and 'p.' to the row2 element.

The expected output is:

col2
p.C29[40]
p.D1305_D1306delinsKK
p.E602C[20]

I am running following query, which runs without an error but the expected output is missing.

SELECT  *,
        CASE     
                WHEN t.c LIKE 'p.?=[%'
                THEN 'p.'|| t.c || ']'

                ELSE 'p.'|| t.c

         END AS col2

FROM table;

Upvotes: 1

Views: 362

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627545

You may use two chained REGEXP_REPLACE calls:

SELECT REGEXP_REPLACE(REGEXP_REPLACE('C29[40', '^(.*\[\d+)$', 'p.\1]'), '^(?:p\.)?', 'p.')

See the regex demo #1 and regex demo #2 and the PostgreSQL demo.

Pattern details

  • ^ - start of string
  • (.*\[\d+) - Group 1 (\1): any 0+ chars as many as possible (.*), then[ and 1+ digits
  • $ - end of string.

The ^(?:p\.)? pattern matches an optional p. substring at the beginning of the string, and thus either adds p. or replaces p. with p. (thus, keeping it).

Upvotes: 2

Related Questions