PostgreSQL UPDATE 子串替换 [英] PostgreSQL UPDATE substring replacement

问题描述

我在测试数据库中有几行，其中值的前缀是美元符号.我想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 '$%';

这篇关于PostgreSQL UPDATE 子串替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！