BigQuery Regex Replace only works when it has one character in pattern

Question

For the below query (Standard SQL), it should remove all column names.

WITH table AS (SELECT "abc" aa, 0 b, NULL c, "xyz" d, 0 e, 0.0 f UNION ALL
               SELECT NULL, NULL, NULL, NULL, NULL, NULL )
SELECT *,
REGEXP_REPLACE(TO_JSON_STRING(t), r'"[a-zA-Z0-9_]":', "") AS check 
FROM table t

But it only works when the column name is single character. It doesn't work for more than one character. Need help on this

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
WITH table AS (
  SELECT "abc" aa, 0 b, NULL c, "xyz" d, 0 e, 0.0 f UNION ALL
  SELECT NULL, NULL, NULL, NULL, NULL, NULL 
)
SELECT *, FORMAT('%t', t) AS check
FROM table t   

with result

Row aa      b       c       d       e       f       check    
1   abc     0       null    xyz     0       0.0     (abc, 0, NULL, xyz, 0, 0.0)  
2   null    null    null    null    null    null    (NULL, NULL, NULL, NULL, NULL, NULL)       

If you want to have double quotes for values - use FORMAT('%T', t) so result will be like ("abc", 0, NULL, "xyz", 0, 0.0)

Meantime, the simple fix was - REGEXP_REPLACE(TO_JSON_STRING(t), r'"[a-zA-Z0-9_]*":', "") AS check - note added *

#standardSQL
WITH table AS (
  SELECT "abc" aa, 0 b, NULL c, "xyz" d, 0 e, 0.0 f UNION ALL
  SELECT NULL, NULL, NULL, NULL, NULL, NULL 
)
SELECT *, 
  REGEXP_REPLACE(TO_JSON_STRING(t), r'"[a-zA-Z0-9_]*":', "") AS check 
FROM table t   

with result

Row aa      b       c       d       e       f       check    
1   abc     0       null    xyz     0       0.0     {"abc",0,null,"xyz",0,0}     
2   null    null    null    null    null    null    {null,null,null,null,null,null}