sql - 数据库sql替换语句更新记录

Question

我在postgresql数据库示例表s_attrs属性中有很多记录

sex = female, age = 32 years, disease = hepatitis B:DOID:2043
sex = male, age = 35 years, disease = hepatitis B:DOID:2043
sex = male, age = 34 years, disease = hepatitis B:DOID:2043
sex = male, age = 55 years, disease = hepatitis B:DOID:2043
sex = male, age = 37 years, disease = hepatitis B:DOID:2043
sex = female, age = 31 years, disease = hepatitis B:DOID:2043

我想改成喜欢

sex="female", age="32 years", disease="hepatitis B:DOID:2043"
sex="male", age="35 years", disease="hepatitis B:DOID:2043"
sex="male", age="34 years", disease="hepatitis B:DOID:2043"
sex="male", age="55 years", disease="hepatitis B:DOID:2043"
sex="male", age="37 years", disease="hepatitis B:DOID:2043"
sex="female", age="31 years", disease="hepatitis B:DOID:2043"

删除等号之间的空格并添加引号，我该如何改变它。我想使用更新和替换sql，但我不知道该怎么做

Answer 1

假设您的表中有一个 id 列，您的基本查询可能如下所示

WITH attr_explode AS 
(
  SELECT id, unnest(string_to_array(s_attrs, ',')) attr
    FROM Table1 
)
SELECT id, array_to_string(array_agg(concat(trim(split_part(attr, '=', 1)), '="', trim(split_part(attr, '=', 2)), '"')), ',') s_attrs
  FROM attr_explode
 GROUP BY id

输出:

| ID |                                                     S_ATTRS |
--------------------------------------------------------------------
|  1 | sex="female",age="32 years",disease="hepatitis B:DOID:2043" |
|  2 |   sex="male",age="35 years",disease="hepatitis B:DOID:2043" |
 ...

这是 SQLFiddle 演示

现在更新你可以做的

WITH attr_explode AS 
(
  SELECT id, unnest(string_to_array(s_attrs, ',')) attr
    FROM Table1 
), attr_replace AS
(
  SELECT id, array_to_string(array_agg(concat(trim(split_part(attr, '=', 1)), '="', trim(split_part(attr, '=', 2)), '"')), ',') s_attrs
    FROM attr_explode
   GROUP BY id
)
UPDATE Table1 t 
   SET s_attrs = r.s_attrs
  FROM attr_replace r 
 WHERE t.id = r.id 

这是 SQLFiddle 演示