How can I replace the last character of a string in PostgreSQL?

Question

In PostgreSQL 11...

I have a "person" table that has these 4 boolean fields:

person
---------
user
employee
supplier
customer

Now I want to return in a query a text formated with the rules of a person in the system. For example:

If the person is just a customer, return 'customer';

If is supplier and customer, return 'supplier and customer';

If is employee, supplier and customer: 'employee, supplier and customer'

First I check the columns and return the corresponding text using CASE. Then I used the "concat_ws" function to bring only what is of value, looking like this:

SELECT
    concat_ws(', ',
        CASE WHEN user THEN 'user' END,
        CASE WHEN employee THEN 'employee' END,
        CASE WHEN supplier THEN 'supplier' END,
        CASE WHEN customer THEN 'customer' END
    ) AS rules
FROM
    person

Result: 'customer' 'supplier, customer' employee, supplier, customer '

With that I almost got to the result, but I need to concatenate "and" at the end when there is more than one value.

Does anyone have an idea how to solve or a more friendly way?

Are other combinations possible, for example employer and customer, or user and supplier? — GMB
– GMB, Commented Jan 31, 2020 at 20:57
@GMB Yes! There are even 3 more rules, being possible to have several combinations. — Poliano Martini
– Poliano Martini, Commented Jan 31, 2020 at 21:35

GMB · Accepted Answer · 2020-01-31 21:52:31Z

2

One solution would be to wrap the expression in regexp_replace()

regexp_replace(
    concat_ws(', ',
        CASE WHEN user THEN 'user' END,
        CASE WHEN employee THEN 'employee' END,
        CASE WHEN supplier THEN 'supplier' END,
        CASE WHEN customer THEN 'customer' END
    ),
    ', (\w+)$',
    'and \1'
)

The regex searches for the last sequence of a comma followed by a variable number of word characters (and captures it) and replaces it with and followed by the captured word.

edited Jan 31, 2020 at 21:52

answered Jan 31, 2020 at 21:45

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Poliano Martini Over a year ago

Almost that! Created the "and" in the last concatenation but also brought a comma in the last value: employee, supplier and , customer

GMB Over a year ago

@Renato: yes I was just fixing this... Thanks.

Collectives™ on Stack Overflow

How can I replace the last character of a string in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related