How can I split a string into two based on a character inbetween using REGEXP_EXTRACT?

I need to split a string into two based on a character and I 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

How can I solve this with REGEXP_EXTRACT?

Answer

Consider the below query for your requirement:

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

Result:

EDIT: Based on your new requirement:

• Sample Input: fruit=apples and oranges

• Expected Output: "fruit" in one column and "apples and oranges" in another column

Consider the below query:

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

Result: