PostgreSQL UPDATE 子串替换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27152413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL UPDATE substring replacement
提问by a_horse_with_no_name
I have a few rows in a test database where there are dollar signs prefixed to the value. I want to UPDATEthe values in the namerow of the test1table however when I threw the following query together it emptied the six rows of data in the namecolumn...
我在测试数据库中有几行，其中有美元符号作为前缀。我想要表行中UPDATE的值，但是当我将以下查询放在一起时，它清空了列中的六行数据......nametest1name
UPDATE test1 SET name=overlay('$' placing '' from 1 for 1);
So "$user" became "" when I intended for that column/row value to become "user".
因此，当我打算将该列/行值变为“用户”时，“$user”变为“”。
How do I combine UPDATE and a substr replacement without deleting any of the other data?
如何在不删除任何其他数据的情况下组合 UPDATE 和 substr 替换？
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.
美元符号仅在出现时作为第一个字符出现。
回答by a_horse_with_no_name
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 whereclause to only change those rows where the column actually starts with a $
如果您想替换$您可以使用的唯一值的开头substr()和一个where子句，以仅更改列实际以 a 开头的那些行$
update test1
set name = substr(name, 2)
where name like '$%';
