如何在Redshift SQL中将类CSV格式字符串拆分至多列？

执行SQL语句 select preferences from stores 后，得到如下查询结果：

请问能否将其转换为如下多列形式的查询结果？

当然可以实现，具体方法取决于你使用的数据库，以下是几种常见数据库的实现方式：

PostgreSQL #

PostgreSQL支持JSON处理，可先将字符串转换为JSON格式，再提取对应字段：

SELECT
  (prefs->>'debit_rate')::numeric AS debit_rate,
  (prefs->>'credit_rate_1')::numeric AS credit_rate_1,
  (prefs->>'credit_rate_2')::numeric AS credit_rate_2
FROM (
  SELECT replace(replace(preferences, '=>', ':'), '"', '"')::jsonb AS prefs
  FROM stores
) t;

也可通过正则表达式直接提取值：

SELECT
  regexp_replace(regexp_match(preferences, '"debit_rate"=>"([0-9.]+)"'), '["()]', '', 'g') AS debit_rate,
  regexp_replace(regexp_match(preferences, '"credit_rate_1"=>"([0-9.]+)"'), '["()]', '', 'g') AS credit_rate_1,
  regexp_replace(regexp_match(preferences, '"credit_rate_2"=>"([0-9.]+)"'), '["()]', '', 'g') AS credit_rate_2
FROM stores;

MySQL #

通用方法（适用于所有版本） #

使用SUBSTRING_INDEX结合REPLACE提取每个字段值：

SELECT
  REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(preferences, '"debit_rate"=>"', -1), '"', 1), ',', '') AS debit_rate,
  REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(preferences, '"credit_rate_1"=>"', -1), '"', 1), ',', '') AS credit_rate_1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(preferences, '"credit_rate_2"=>"', -1), '"', 1) AS credit_rate_2
FROM stores;

MySQL 8.0+ 版本（利用JSON函数） #

先将字符串转换为JSON格式，再提取字段：

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(prefs, '$.debit_rate')) AS debit_rate,
  JSON_UNQUOTE(JSON_EXTRACT(prefs, '$.credit_rate_1')) AS credit_rate_1,
  JSON_UNQUOTE(JSON_EXTRACT(prefs, '$.credit_rate_2')) AS credit_rate_2
FROM (
  SELECT REPLACE(preferences, '=>', ':') AS prefs
  FROM stores
) t;

SQL Server #

使用字符串截取函数提取对应值（适用于SQL Server 2016+）：

SELECT
  TRIM('"' FROM SUBSTRING(preferences, CHARINDEX('"debit_rate"=>"', preferences)+15, CHARINDEX('"', preferences, CHARINDEX('"debit_rate"=>"', preferences)+15) - (CHARINDEX('"debit_rate"=>"', preferences)+15))) AS debit_rate,
  TRIM('"' FROM SUBSTRING(preferences, CHARINDEX('"credit_rate_1"=>"', preferences)+19, CHARINDEX('"', preferences, CHARINDEX('"credit_rate_1"=>"', preferences)+19) - (CHARINDEX('"credit_rate_1"=>"', preferences)+19))) AS credit_rate_1,
  TRIM('"' FROM SUBSTRING(preferences, CHARINDEX('"credit_rate_2"=>"', preferences)+19, CHARINDEX('"', preferences, CHARINDEX('"credit_rate_2"=>"', preferences)+19) - (CHARINDEX('"credit_rate_2"=>"', preferences)+19))) AS credit_rate_2
FROM stores;

内容的提问来源于stack exchange，提问作者Marcio Veiga