CODEWITHSUNDEEP
postgresql
Poliano Martini
Poliano Martini

Reputation: 53

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

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?

Upvotes: 0

Views: 1399

Answers (1)

GMB
GMB

Reputation: 222702

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.

Upvotes: 2

Related Questions