PostgreSQL UPDATE substring replacement

postgresql, regex

Solution

If you want to replace all dollar signs, use this:

update test1 
   set name = replace(name, '$', '');

If you want to replace the `$` only at the beginning of the value you can use `substr()` and a `where` clause to only change those rows where the column actually starts with a `$`

update test1 
    set name = substr(name, 2)
where name like '$%';

Problem

I have a few rows in a test database where there are dollar signs prefixed to the value. I want to `UPDATE` the values in the `name` row of the `test1` table however when I threw the following query together it emptied the six rows of data in the `name` column... ``` UPDATE test1 SET name=overlay('$' placing '' from 1 for 1); ``` So "$user" became "" when I intended for that column/row value to become "user". How do I combine UPDATE and a substr replacement without deleting any of the other data? If there isn't a dollar sign I want the row to remain untouched. The dollar sign only occurs as the first character when it does occur.

Original source