在SQL中解析单个属性

I have a description attribute which has all the below information.

我有一个描述属性，其中包含以下所有信息。

 Subject:
 Security ID:   SXXX 
 Account Name:  GXXX$
 Account Domain: GGGG
 Security ID:   SXXX0
 Account Name: NETWORK 
 Account Domain: AUTHORITY

Workstation Name:

工作站名称：

I am trying to parse this attribute and get the different parts of it and store it in a separate column. For example, Subject goes into a separate column, Security ID goes into a separate column and so on.

我试图解析这个属性并获取它的不同部分并将其存储在一个单独的列中。例如，Subject进入一个单独的列，Security ID进入一个单独的列，依此类推。

Any help would be appreciated.

任何帮助，将不胜感激。

1 个解决方案

#1

Try below as an example of using Regular expression functions in BigQuery

请尝试以下作为在BigQuery中使用正则表达式函数的示例

SELECT
  REGEXP_EXTRACT(description, 'Subject:([\\s\\S\\w\\W]*?)Security ID:') AS subject,

  REGEXP_EXTRACT(description, 'Security ID:([\\s\\S\\w\\W]*?)Account Name:') AS securityId_1,
  REGEXP_EXTRACT(description, 'Account Name:([\\s\\S\\w\\W]*?)Account Domain:') AS accountName_1,
  REGEXP_EXTRACT(description, 'Account Domain:([\\s\\S\\w\\W]*?)Security ID:') AS accountDomain_1,

  REGEXP_EXTRACT(description, 'Account Domain:[\\s\\S\\w\\W]*?Security ID:([\\s\\S\\w\\W]*?)Account Name:') AS securityId_2,
  REGEXP_EXTRACT(description, 'Security ID:[\\s\\S\\w\\W]*Account Name:([\\s\\S\\w\\W]*?)Account Domain:') AS accountName_2,
  REGEXP_EXTRACT(description, 'Account Name:[\\s\\S\\w\\W]*Account Domain:([\\s\\S\\w\\W]*)') AS accountDomain_2,

FROM
  (SELECT ' Subject: Test1
   Security ID:   SXXX 
   Account Name:  GXXX$
   Account Domain: GGGG
   Security ID:   SXXX0
   Account Name: NETWORK 
   Account Domain: AUTHORITY' AS description 
  ),
  (SELECT 'Subject: Test2 Security ID: SXXX Account Name: GXXX$ Account Domain: GGGG Security ID: SXXX0 Account Name: NETWORK Account Domain: AUTHORITY ' AS description)