Presto Replace Multiple Characters
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...
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)
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
