尝试使用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 | +-----------------------------------+-----------------+