Trying to split one column to multiple columns using Snowflake sql

data need to get split from SOID column to Circ,Language,Words as show above in pic. When trying with below logic :-

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;

the data is getting handled correctly, But as per the pic highlighted in yellow some data is not getting captured with the above logic. Instead of shoing language as English, it is shwing null, and circuit as Biotechnology…. it is displaying null as showing in the pic. Please need your inputs.

Answer

It seems the issue is related to handling the “(s)” parts:

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         |
+-----------------------------------+-----------------+