presto replace multiple characters

Publish date: 2024-05-16

I have a string and would like to remove a set of characters. Is there a better way than chaining multiple replace()?

I came up with the following but it requires a replace() call for each character:

 WITH my_table(id, a_string) AS ( VALUES (1, 'test{' || CHR(39) || '}xyz'), (2, 'x'), (3, '{y}'), (1, 'z'), (2, 'none'), (3, 'none' || CHR(39) || 'xyz') ) SELECT replace(replace(replace(a_string,CHR(39)),'{'),'}') FROM my_table 

This is a minimal example and I have more characters to escape so I was looking for a less verbose way of achieving this. I didn't find a string function in the documentation capable of this directly.

Thank you.

1 Answer

You can use regexp_replace to remove multiple characters:

presto> WITH my_table(id, a_string) AS ( -> VALUES -> (1, 'test{' || CHR(39) || '}xyz'), -> (2, 'x'), -> (3, '{y}'), -> (1, 'z'), -> (2, 'none'), -> (3, 'none' || CHR(39) || 'xyz') -> ) -> SELECT regexp_replace(a_string, '[{}'']') FROM my_table; _col0 --------- testxyz x y z none nonexyz (6 rows) 
1

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobW1oYWuEd36Oqameq6SkerOxz6WYnJ1dosKtwMipo55lk52us63CrZyrqw%3D%3D