我正在使用 PostgreSQL，我的表有 2 列 manager_id 和 manager_name。我想將 manager_name 列（具有如下值：ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA）拆分為 Manager name| 城市| 國家。分隔符 "-" 和 "," 。
我試過這個查詢，但它只顯示結果，沒有永久保存這些列select manager_name ,split_part(manager_name, '-', 1) m_name ,split_part(manager_name, ',', 2) city ,split_part(manager_name, ',', 2) country from manager2
有沒有其他方法可以拆分此列并將其永久保存在該表中？
uj5u.com熱心網友回復：
也許有點冗長，但以下似乎可以做到。您SPLIT_PART()從manager_name需要一些調整中檢索城市；還將它們包裝起來TRIM()以洗掉前導和尾隨空格。
CREATE TABLE manager2
(
manager_id SERIAL PRIMARY KEY,
manager_name VARCHAR(250)
);
INSERT INTO manager2 (manager_name)
VALUES ('ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA');
INSERT INTO manager2 (manager_name)
VALUES ('HARTMANN REEDEREI - LEER, GERMANY');
-- add columns to table
ALTER TABLE manager2 ADD COLUMN m_name VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN city VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN country VARCHAR(250);
-- populate tables with vals
WITH results AS (
SELECT manager_id AS id, TRIM(SPLIT_PART(manager_name, '-', 1)) AS m_name,
TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1)) AS city,
TRIM(SPLIT_PART(manager_name, ',', 2)) AS country
FROM manager2
)
UPDATE manager2
SET
m_name = (SELECT m_name FROM results WHERE manager_id = results.id),
city = (SELECT city FROM results WHERE manager_id = results.id),
country = (SELECT country FROM results WHERE manager_id = results.id);
更新后的表格：
manager_id | manager_name | m_name | city | country
------------ ------------------------------------------------- ------------------------------ ----------- ---------
1 | ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA | ANGLO EASTERN SHIPMANAGEMENT | HONG KONG | CHINA
2 | HARTMANN REEDEREI - LEER, GERMANY | HARTMANN REEDEREI | LEER | GERMANY
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/481673.html
標籤：PostgreSQL 关系型数据库