Substring replacement for PostgreSQL UPDATE

Question

I have several rows in a test database where there are dollar signs preceded by a value. I want UPDATE values ​​in the name row of table test1 , however, when I missed the next query, it cleared six data rows in the name column ...

 UPDATE test1 SET name=overlay('$' placing '' from 1 for 1); 

So, "$ user" became "when I intended this column / row value to become" user ".

How to combine UPDATE and substr substr without deleting any other data?

If the dollar sign is not needed, I want the string to remain untouched.

The dollar sign appears only as the first character when this occurs.

Answer 1

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

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

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

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