I have a table, which can be simplified to this:

CREATE TABLE products
(
    id serial primary key,
    product_id  varchar
)

In product_id column stored some data with format Product-somestring. In some rows, Product can start from the UPPERCASE and contain - as a delimiter. The goal is to UPDATE all the rows and change first part (splitted by -) of product_id value to LOWERCASE.

I see 2 cases:

1. Where we have 1 -
2. Where we have 2 or more -

I gave birth to this huge and ugly query (but it works):

UPDATE products
SET product_id =
    CASE
        WHEN array_length(string_to_array(product_id, '-'), 1) >= 2 THEN array_to_string(array_replace(string_to_array(product_id, '-'), (string_to_array(product_id, '-'))[1], LOWER((string_to_array(product_id, '-'))[1])),'-')
        WHEN array_length(string_to_array(product_id, '-'), 1) = 1 THEN LOWER(product_id)

    END
WHERE product_id ~ '[[:upper:]]';

My question is:

How can it be simplified or what is the correct way of solving this problem?

CodePudding user response：

Try this

update product
set product_id = replace(product_id,substring(product_id,1,position('-' in product_id)), lower(substring(product_id,1,position('-' in product_id))));