PostgreSQL UPDATE 子串替换

本文介绍了PostgreSQL UPDATE 子串替换的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在测试数据库中有几行，其中值的前缀是美元符号.我想UPDATE test1 表的name 行中的值但是当我将以下查询放在一起时它清空了六行数据在 name 列中...

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);

因此，当我打算将该列/行值变为用户"时，$user"变为".

So "$user" became "" when I intended for that column/row value to become "user".

如何在不删除任何其他数据的情况下组合 UPDATE 和 substr 替换?

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.

推荐答案

如果要替换所有美元符号，请使用:

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

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

如果你只想替换值开头的 $ 你可以使用 substr() 和一个 where 子句来只更改列实际以 $

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 '$%';