Redshift Translate command to replace characters

Gary A 0
Viewed 14

I need to translate commas in a column to pipe with with spaces on each side in Redshift ('a,b,c' becomes 'a | b | c' using Translate. Something in this statement is not giving me my desired results and I can't figure out why? select 'a,b,c' as comma_string, translate(comma_string, ',', ' | ' ) as pipe_string

is yielding 'a b c' with no pipes. Having trouble getting the space before and after the pipe as

select 'a,b,c' as comma_string, translate(comma_string, ',', '|' ) as pipe_string gives me 'a|b|c'

translate amazon-redshift replace
2 Answers

The REPLACE command works for this. NOt sure why Translate doesn't.

select 'a,b,c' as comma_string, REPLACE(comma_string, ',' ,' | ') as pipe_string
yields the desired result 'a | b | c'

Gary A0

You would need to use REPLACE since TRANSLATE only maps single characters:

TRANSLATE is similar to the REPLACE function and the REGEXP_REPLACE function, except that REPLACE substitutes one entire string with another string and REGEXP_REPLACE lets you search a string for a regular expression pattern, while TRANSLATE makes multiple single-character substitutions.

https://docs.aws.amazon.com/redshift/latest/dg/r_TRANSLATE.html

MP240
Related