presto replace multiple characters
Publish date: 2024-05-16
![](https://cdn.statically.io/img/cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png)
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)
1ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobW1oYWuEd36Oqameq6SkerOxz6WYnJ1dosKtwMipo55lk52us63CrZyrqw%3D%3D