How I can spilit data from one column to other column

[NewinBigquery]

WITH Test_1 AS
(SELECT
chat_start_url, chat_start_date_Asia_Kolkata, referrer, chat_duration_in_seconds, visitor_nick, visitor_ip, visitor_email,
ROW_NUMBER() OVER (partition by visitor_ip) as IP
FROM bigquery-405008.xxxx.xx_chat

SELECT *FROM Test_1

I want to spilt data from one column to other column in same table, where column name is chat_start_url. Which are containing 2 hyperlinks and wanting spilt 1 hyperlink with new column.

[ms4446]

To split the chat_start_url column into two separate columns, you can use the SPLIT() function. This function will divide the URL string into an array of strings based on a specified delimiter. Here's how you can modify your query:

WITH Test_1 AS ( 
  SELECT 
    chat_start_url,
    chat_start_date_Asia_Kolkata,
    referrer,
    chat_duration_in_seconds,
    visitor_nick,
    visitor_ip,
    visitor_email,
    ROW_NUMBER() OVER(PARTITION BY visitor_ip) AS IP 
  FROM `bigquery-405008.xxxx.xx_chat`
) 
SELECT 
  IP,
  chat_start_url,
  chat_start_date_Asia_Kolkata,
  referrer,
  chat_duration_in_seconds,
  visitor_nick,
  visitor_ip,
  visitor_email,
  SPLIT(chat_start_url, '|')[SAFE_OFFSET(0)] AShyperlink1,
  SPLIT(chat_start_url, '|')[SAFE_OFFSET(1)] AS hyperlink2 
FROM Test_1

In this query:

• SPLIT(chat_start_url, '|') splits the chat_start_url into an array of strings using '|' as the delimiter.
• [SAFE_OFFSET(0)] and [SAFE_OFFSET(1)] are used to safely access the first and second elements of the array. If the element at the specified offset does not exist, it returns NULL instead of causing an error.
• hyperlink1 and hyperlink2 are the new columns created to store the first and second hyperlinks, respectively.