Replace string by using Regular Expression

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: