在整个表中用另一个字符串替换一个字符串的最佳方法是什么？ [英] What is the best way to replace a string with another string on entire table?

问题描述

我的问题与下面类似，但我想将其替换为任何列：

My question is similar to below but I want to replace it for any column:

postgresql-替换文本字段中字符串的所有实例

例如-在整个表格（所有行）中用cat替换cat的所有实例

for instance - replace all instances of cat with dog in entire table(all rows)

我希望下面的查询对所有列都有效（无需指定

I am expecting something like below query to work for all columns(without specifying the column name in update query).

UPDATE table_name SET information_schema.columns replace (information_schema.columns, 'old_value', 'new_value');

实际上似乎不起作用。

有什么建议吗？

推荐答案

create table sample (s varchar,s1 varchar,s2 varchar,s3 varchar,s4 varchar);

insert into sample values ('A','A','A','A','A');
insert into sample values('AB','AB','A','AB','AB');
insert into sample values('A','AB','A','AB','A');
insert into sample values('CA','A','A','CA','CA');

select * from sample 

并尝试这样

create or replace function f1(_table text,_oldVal text,_newVal text) returns void as 
$$
declare 
rw record;
begin
for rw in 
    select 'UPDATE '||$1||' SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||','''||$2||''','''||$3||'''); ' QRY
    FROM (select column_name from information_schema.columns where table_schema='public' and table_name =$1)c
loop
    EXECUTE rw.QRY;
end loop;
end;
$$language plpgsql

并致电

select f1('sample','A','Z')

select * from sample 

---

OR

---

OR

do 
$$
declare 
rw record;
begin
for rw in 
    select 'UPDATE sample SET '||C.COLUMN_NAME||' = REPLACE ('||C.COLUMN_NAME||',''Z'',''A''); ' QRY
    FROM (select column_name from information_schema.columns where table_schema='public' and table_name ='sample')c
loop
    EXECUTE rw.QRY;
end loop;
end;
$$;

select * from sample 

这篇关于在整个表中用另一个字符串替换一个字符串的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！