PostgreSQL SELECT and REPLACE multiple strings into a single column

Question

What is the optimal way to replace multiple strings and return the results of the replace operations in a single column.

The following query works perfectly, however it generated 5 columns obviously.

SELECT 
REPLACE(l.status, 'New Onboarding Request', '1. New Lead'),
REPLACE(l.status, 'Appointment Accepted', '2. Appointment Accepted'),
REPLACE(l.status, 'Accepted', '3. Property Eligible'),
REPLACE(l.status, 'Owner Accepted', '4. Terms Accepted'),
REPLACE(l.status, 'Onboarding', '5. Onboarding'), 
l.id, o.name as partner, v.lat as latitude, v.lng as longitude, v.postcode, 
l.status, v.bedrooms, v.bathrooms, "v"."houseOrFlat", to_char("v"."created_at", 'YYYYMMDD') as valuation_date, to_char("l"."created_at", 'YYYYMMDD') as referral_date, to_char("l"."updated_at", 'YYYYMMDD') as updated,  v.estimated_nightly_rate, v.furnished   FROM valuations v
LEFT JOIN agent_onboarding_requests l on v.id = l.valuation_id
INNER JOIN organisations o on o.id = l.organisation_id
WHERE l.organisation_id != 1
ORDER BY referral_date ASC

the simplest and obvious is nested replace... REPLACE(REPLACE(l.status, 'New Onboarding Request', '1. New Lead'), 'Appointment Accepted', '2. Appointment Accepted')) and so on — Vao Tsun
– Vao Tsun, Commented May 4, 2018 at 13:32

thibautg · Accepted Answer · 2018-05-05 20:50:13Z

You can also create a inline table with VALUES and join it to your existing table:

SELECT
    s.status old_status,
    n.new_status
FROM
  statuses s
JOIN
  (VALUES ('New Onboarding Request', '1. New Lead'),
          ('Appointment Accepted','2. Appointment Accepted'),
          ('Accepted', '3. Property Eligible'),
          ('Owner Accepted', '4. Terms Accepted'),
          ('Onboarding', '5. Onboarding')) n(old_status, new_status)
  ON s.status = n.old_status

or with a CTE (WITH):

WITH n(old_status, new_status) AS
(VALUES ('New Onboarding Request', '1. New Lead'),
        ('Appointment Accepted','2. Appointment Accepted'),
        ('Accepted', '3. Property Eligible'),
        ('Owner Accepted', '4. Terms Accepted'),
        ('Onboarding', '5. Onboarding'))
SELECT
    s.status old_status,
    n.new_status
FROM
  statuses s
JOIN n ON s.status = n.old_status

larpo · Accepted Answer · 2018-05-05 19:33:03Z

1

In the end this struck me as a more elegant way than nested REPLACE

SELECT 

    CASE 
        WHEN(l.status = 'New Onboarding Request') THEN '1. New Lead'
        WHEN(l.status = 'Appointment Accepted') THEN '2. Appointment Accepted'
        WHEN(l.status = 'Accepted') THEN '3. Property Eligible'
        WHEN(l.status = 'Owner Accepted') THEN '4. Terms Accepted'
        WHEN(l.status = 'Onboarding') THEN '5. Onboarding'
    END AS status_modified,

edited May 5, 2018 at 19:33

answered May 4, 2018 at 20:36

larpo

1,2231 gold badge11 silver badges18 bronze badges

Collectives™ on Stack Overflow

PostgreSQL SELECT and REPLACE multiple strings into a single column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related