By parsing JSON array, you get values in brackets and double quotas and might need to make the string more human readable. Regular expression REGEXP_REPLACE can be used for it.
Input string: [“Blue”], [“Pink”], [“Black”]
Output string: Blue; Pink; Black
Action: Get rid of brackets with double quotas and replace comma by semicolon.
Code:
SELECT
'["Blue"], ["Pink"], ["Black"]' as original_string,
REGEXP_REPLACE(original_string, '[^A-Za-z,]', '') AS alpha_and_comma_chars_only,
REGEXP_REPLACE(alpha_and_comma_chars_only, ',', '; ') AS replaced_comma
;Result:
| original_string | alpha_and_comma_chars_only | replaced_comma |
| [“Blue”], [“Pink”], [“Black”] | Blue,Pink,Black | Blue; Pink; Black |
Explanation:
