Iterate over a record in BQ to fetch specific content

Question

I have a record where there're multiple comment made by users and I want to fetch all of them. Record looks like:

The use case here is to fetch only the comment part. I'm able to fetch the comments successfully if there's only one. But in case of multiples, it doesn't work. SELECT approver_comments_txt as f0, IF( CONTAINS_SUBSTR(TRIM(approver_comments_txt), ']'), IF(SUBSTR(TRIM(approver_comments_txt),(STRPOS(TRIM(approver_comments_txt),']') + 1),LENGTH(TRIM(approver_comments_txt))) != '', SUBSTR(TRIM(approver_comments_txt),(STRPOS(TRIM(trim(approver_comments_txt)),']') + 1),LENGTH(TRIM(approver_comments_txt))), NULL), TRIM(approver_comments_txt) ) as f1 FROM tbl_name

please let me know if there's a feasible way to do so. Thanks in Advance.

Answer 1

I just removed the distinct to capture multiple comment capture in my testing, Please see result:

WITH
  cte AS (
  SELECT
    'campaign1[asd]a' AS approver_comments_txt,
  UNION ALL
  SELECT
    'campaign2[aaaa]comment here' AS approver_comments_txt,
  UNION ALL
  SELECT
    'campaign2[1231]' AS approver_comments_txt,
  UNION ALL
  SELECT
    'campaign2' AS approver_comments_txt )
SELECT
  approver_comments_txt AS f0,
IF
  ( CONTAINS_SUBSTR(TRIM(approver_comments_txt), ']'),
  IF
    (SUBSTR(TRIM(approver_comments_txt),(STRPOS(TRIM(approver_comments_txt),']') + 1),LENGTH(TRIM(approver_comments_txt))) != '', SUBSTR(TRIM(approver_comments_txt),(STRPOS(TRIM(trim(approver_comments_txt)),']') + 1),LENGTH(TRIM(approver_comments_txt))), NULL), TRIM(approver_comments_txt) ) AS f1
FROM
  cte