How to split a string into two based on a character inbetween using REGEXP

I need to split a string into two based on a character. Need to do this without using SPLIT command.

What I have:

string
fruit=orange
fruit=apple
vegetable=onion

What I need:

splitstring1    splitstring2
fruit           orange
fruit           apple
vegetable       onion

Could anyone help me solve this with REGEXP_EXTRACT ?

CodePudding user response：

Consider the below query:

SELECT REGEXP_EXTRACT(string, r'^[a-zA-Z] ') AS splitstring1, REGEXP_EXTRACT(string, r'[a-zA-Z] $') AS splitstring2  FROM `project.dataset.table`;

Result:

CodePudding user response：

Consider below approach

select 
  arr[offset(0)] as splitstring1,
  arr[safe_offset(1)] as splitstring2
from  your_table,
unnest([struct(regexp_extract_all(string, r'[^=] ') as arr)])

if applied to sample data in your question - output is