Update same column with multiple values at different positions using Overlay function in Postgres

user_14351609 0
Viewed 201

I have two columns in table test as below. I want to update 2nd position in message with 'K' and 4th position as 'N' where id = 1 using Overlay function. I am able to update 2nd position as per below query but unable to find solution for 4th position.

Could you please provide me some guidance? Thank you.

Table test

    id | message
    ------------
    1  | ABCD
    2  | PQRS

I wrote below query:

update test
set message = overlay(message placing 'K' from 2 for 1)
where id = 1

I got reference from Postgres String Functions and Operators

sql-update overlay postgresql sql string
1 Answers

If you want to do this with overlay(), you need to call it twice:

overlay(overlay('ABCD' placing 'K' from 2 for 1) placing 'N' from 4 for 1)

You could also use substring():

substring(message, 1, 1) || 'K' || substr(message, 3, 1) || 'N'

Or, if message can contain more than 4 characters:

substring(message, 1, 1) || 'K' || substr(message, 3, 1) || 'N' || substr(message, 5)

Finally: a neater option (although probably less efficient) uses a regex:

regexp_replace(message, '^(.)(?:.)(.)(?:.)', '\1K\2N')
user_106767160
Related