我在测试数据库中有几行,其中值的前缀是美元符号.我想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 子串替换
发布评论