本文介绍了尝试使用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将一列拆分为多列
发布评论