postgresql数据库中replace函数替换

select id,value from t_rule;

-- 查询带有函数ifnull的数据
SELECT "id","value" FROM t_rule WHERE "value" ILIKE '%ifnull(%';
SELECT "id","value" FROM t_rule WHERE "value" LIKE '%coalesce(%';
-- 替换掉ifnull为COALESCE()
update t_rule set "value"= REPLACE("value", 'ifnull(','coalesce(') where "value" ILIKE '%ifnull(%';
update t_rule set "value"= REPLACE("value", 'IFNULL(','coalesce(') where "value" ILIKE '%ifnull(%';
select REPLACE("value", 'ifnull(','coalesce(') from t_rule where "value" LIKE '%ifnull(%';

-- 查询带有函数``的数据
select id,value from t_rule where "value" like '%`%`%';
select id,value from t_rule where "value" like '%"%"%';
-- 替换掉`为"
update t_rule set "value"= REPLACE("value", '`','"') where "value" LIKE '%`%`%';
select REPLACE("value", '`','"') from t_rule where "value" LIKE '%`%`%';

-- 查找带有函数date_format函数的数据
select id,value from t_rule where "value" Ilike '%date_format(%';
select id,value from t_rule where "value" Ilike '%to_char(%';
-- 替换date_format为to_char，忽略大小写
update t_rule set "value"= REGEXP_REPLACE("value", 'date_format','to_char','gi') where "value" ILIKE '%date_format(%';
select id,REGEXP_REPLACE("value", 'date_format','to_char','gi') from t_rule where "value" ILIKE '%date_format(%';
-- 查找带有%Y-%m-%d的数据
select id,value from t_rule where "value" Ilike '%\%Y-\%m-\%d%';
select id,value from t_rule where "value" Ilike '%YYYY-MM-DD%';
update t_rule set "value"= REGEXP_REPLACE("value", '\%Y-\%m-\%d','YYYY-MM-DD','gi') where "value" ILIKE '%to_char(%';
select id,REGEXP_REPLACE("value", '\%Y-\%m-\%d','YYYY-MM-DD','gi') from t_rule where "value" ILIKE '%to_char(%';

-- 查找带有函数find_in_set的数据
select "id","value" from t_rule where "value" Ilike '%find_in_set(%';
select "id","value" from t_rule where "value" Ilike '%mysql_find_in_set(%';
-- 替换find_in_set函数成mysql_find_in_set函数
update t_rule set "value"= REGEXP_REPLACE("value", 'find_in_set','mysql_find_in_set','gi') where "value" ILIKE '%find_in_set(%';
update t_rule set "value"= REGEXP_REPLACE("value", 'mysql_find_in_set','find_in_set','gi') where "value" ILIKE '%mysql_find_in_set(%';

-- 查找带有函数field的数据
select "id","value" from t_rule where "value" Ilike '%field(%';
select "id","value" from t_rule where "value" Ilike '%mysql_field(%';
update t_rule set "value"= REPLACE("value", 'field(','mysql_field(') where "value" ILIKE '%field(%';
update t_rule set "value"= REPLACE("value", 'mysql_field(','field(') where "value" ILIKE '%mysql_field(%';

-- 测试pgsql的limit语法问题
select * from t_rule
order by id asc
limit 5 offset 0;

-- 查找带有函数datediff的数据
select "id","value" from t_rule where "value" Ilike '%datediff(%';
-- 查找当前时间与2023-9-21 10:21:23之间的时间差
select mysql_datediff(now(),'2023-9-21 10:21:23');

select "id","type" from t_rule;
-- 测试string_agg函数STRING_AGG()
select "type",mysql_group_concat("id",',') as s from t_rule group by "type";
-- SET search_path = ourea_yc; 将函数和聚合函数的命名空间添加到当前会话中：
select "type",mysql_group_concat(distinct "id",',') as s from t_rule;
-- select "type",string_agg(id,',') from t_rule group by "type";

-- 查询带有函数if()的数据
select "id","value" from t_rule where "value" Ilike '%mysql_if(%';
select "id","value" from t_rule where "value" Ilike '%mysql_group_concat(%';
select "id","value" from t_rule where "value" Ilike '%group_concat(%';
select "id","value" from t_rule where "value" Ilike '%mysql_datediff(%';
select "id","value" from t_rule where "value" Ilike '%concat_ws(%';
select "id","value" from t_rule where "value" Ilike '%concat(%'; --200060
update t_rule set "value"= REPLACE("value", 'concat(','mysql_concat(') where "id" = 200060;
select "id","value" from t_rule where "value" Ilike '%last_insert_id(%';