尝试使用Snowflake SQL将一列拆分为多列

本文介绍了尝试使用Snowflake SQL将一列拆分为多列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

数据需要从SOID列拆分到Circ、Language、Words，如上图所示。尝试使用以下逻辑时：-

SELECT SOID,
 regexp_substr(SALES_ORDER_ITEM_DESCRIPTION, 'Circuit:\s([a-zA-Z0-9 ]*)(,\s|$)', 1, 1, 'e') AS "Circuit",
 regexp_substr(SALES_ORDER_ITEM_DESCRIPTION, 'Language\(s\):\s([a-zA-Z0-9, ]+)(,\s|$)', 1, 1, 'e') AS "Language",
 regexp_substr(SALES_ORDER_ITEM_DESCRIPTION, 'Words:\s([a-zA-Z0-9 ]*)(,\s|$)', 1, 1, 'e') AS "Words"
FROM XYZ;

数据得到了正确处理，但根据以黄色突出显示的图片，上面的逻辑没有捕获某些数据。它没有把语言说成是英语，而是把语言说成是空的，把巡回说成是生物技术……它显示为空，如图所示。请需要您的输入。

推荐答案

该问题似乎与处理"；(S)部件有关：

with XYZ as (
  select 'Attachments: 1, Circuit: North America, Language: English, Words: 400' as SALES_ORDER_ITEM_DESCRIPTION
  union all
  select 'Attachments: 1, Circuit: North America, Language(s): English,Spanish, Words: 500' as SALES_ORDER_ITEM_DESCRIPTION
  union all
  select 'Attachments: 1, Circuit: Biotechnology Newsline [National], Language(s): English, Words: 600' as SALES_ORDER_ITEM_DESCRIPTION
)
SELECT
regexp_substr(SALES_ORDER_ITEM_DESCRIPTION, 'Circuit:\s([a-zA-Z0-9 \[\]]+)(,\s|$)', 1, 1, 'e') AS "Circuit",
regexp_substr(SALES_ORDER_ITEM_DESCRIPTION, 'Language[()s]*:\s([a-zA-Z0-9\, ]+)(,\s|$)', 1, 1, 'e') AS "Language"
FROM XYZ;


+-----------------------------------+-----------------+
|              Circuit              |    Language     |
+-----------------------------------+-----------------+
| North America                     | English         |
| North America                     | English,Spanish |
| Biotechnology Newsline [National] | English         |
+-----------------------------------+-----------------+

这篇关于尝试使用Snowflake SQL将一列拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！