⨳ A Function to Mask Strings ⨳

CREATE TEMPORARY FUNCTION mask_string(str STRING, n INT64) AS (
  -- keep first n characters of a string, mask remaining characters
  CASE
    WHEN REGEXP_CONTAINS(TRIM(str), r'^[0-9-]{8,}$') -- only digits
    THEN CONCAT(TRIM(SUBSTR(str, 1, n)), REGEXP_REPLACE(TRIM(SUBSTR(str,n+1)),r'[0-9]', '#'))
    WHEN REGEXP_CONTAINS(TRIM(str), r'^[a-zA-Z-]{8,}$') -- only letters
    THEN CONCAT(TRIM(SUBSTR(str, 1, n)),REGEXP_REPLACE(TRIM(SUBSTR(str,n+1)),r'[a-zA-Z]', '@'))
    WHEN REGEXP_CONTAINS(TRIM(str), r'^[a-fA-F0-9-]{8,}$') -- hex-like strings
    THEN CONCAT(TRIM(SUBSTR(str, 1, n)),REGEXP_REPLACE(TRIM(SUBSTR(str,n+1)),r'[a-fA-F0-9]', '⨳'))
    ELSE CONCAT(TRIM(SUBSTR(str, 1, n)),REGEXP_REPLACE(TRIM(SUBSTR(str,n+1)),r'.', '*'))
  END
);

SELECT NUMERIC_ID , mask_string(NUMERIC_ID, 1) AS masked_NUMBERID_ID_1,
  HEX_ID, mask_string(HEX_ID, 0) AS masked_HEX_ID_0, mask_string(HEX_ID, 2) AS masked_HEX_ID_2,
  MESSY_ID , mask_string(MESSY_ID, 1) AS masked_MESSY_ID_1
FROM ( -- fake some data
  SELECT
    SUBSTR(FORMAT("%17.15f", RAND()), 3) AS NUMERIC_ID ,
    SUBSTR(GENERATE_UUID(), 15) AS HEX_ID ,
    TO_BASE64(SUBSTR(SHA256(FORMAT("%17.15f", RAND())), 15)) AS MESSY_ID
  FROM ( -- get 10 records
    SELECT x FROM UNNEST(GENERATE_ARRAY(1,10)) x
  )
)
;

SELECT mask_string(NUMERIC_ID, 1) AS PATTERN , COUNT(1) AS N
FROM (
  SELECT
    SUBSTR(FORMAT("%17.15f", RAND()), 3) AS NUMERIC_ID ,
    SUBSTR(GENERATE_UUID(), 15) AS HEX_ID ,
    TO_BASE64(SUBSTR(SHA256(FORMAT("%17.15f", RAND())), 15)) AS MESSY_ID
  FROM (
    SELECT x FROM UNNEST(GENERATE_ARRAY(1,100000)) x
  )
)
GROUP BY 1
ORDER BY 1
;