你可以考虑下面

-- sample data
WITH sample_table AS (
 SELECT 'a 11^Gb 22^Gc 33' str UNION ALL
 SELECT 'a AA^Gb BB^Gc CC' str
)
-- query starts here
SELECT str,
       (SELECT AS STRUCT
               STRING_AGG(SPLIT(out, ' ')[OFFSET(0)], '^G') col1,
               STRING_AGG(SPLIT(out, ' ')[OFFSET(1)], '^G') col2
          FROM UNNEST(SPLIT(str, '^G')) out
       ).*
  FROM sample_table;

-- query result
+------------------+---------+------------+
|       str        |  col1   |    col2    |
+------------------+---------+------------+
| a 11^Gb 22^Gc 33 | a^Gb^Gc | 11^G22^G33 |
| a AA^Gb BB^Gc CC | a^Gb^Gc | AA^GBB^GCC |
+------------------+---------+------------+

另一个选择

select str, 
  array_to_string(regexp_extract_all(str, r'(?:([^\s]+) [^\s]*?)(?:\^G|$)'), '^G') as col1,
  array_to_string(regexp_extract_all(str, r'(?:[^\s]+ ([^\s]*?))(?:\^G|$)'), '^G') as col2
from your_table

输出如

您可以使用regex_replace()替换数字或小写字母，并使用replace（）删除空格

select replace(regexp_replace(colname, '[0-9]', ''),' ','')column1, replace(regexp_replace(colname, '[a-z]', ''),' ','')column2