What did I do wrong? UPDATE with condition and LIKE postgres-CodePudding

There is a table and I need to update the data in the 3rd column, according to the condition. If there are brackets, then you need to take information from there, and if not, what is in the first column. Put it where there is no information on the 3rd column. In most cases it is necessary to put where status down or error

https://dbfiddle.uk/GgFft6cY

here is my request:


    UPDATE table_1
    SET name_3 = 
        CASE
            WHEN name_3 != '' THEN name_3
            WHEN name_1 LIKE '%(%' THEN SUBSTRING(name_1 FROM '%(%' FOR ')')
            ELSE name_1
        END
    WHERE status IN ('down', 'error');

ERROR: invalid regular expression: parentheses () not balanced

what's wrong? or can it be done differently?

CodePudding user response：

After FROM and FOR you need a start index and the length, which is a number.

 UPDATE table_1
    SET name_3 = 
        CASE
            WHEN name_3 <> '' THEN name_3
            WHEN name_1 LIKE '%(%' THEN SUBSTRING(name_1 FROM POSITION ('(' in name_1) 1 FOR POSITION (')' in name_1) -POSITION ('(' in name_1)-1)
            ELSE name_1
        END
    WHERE status IN ('down', 'error');

Demo