Search code examples
sqlregexreplaceamazon-redshiftregexp-replace

replace backslash in redshift

I am working with a string column in redshift database where the instance of \" occurs multiple times in the same value. I want to replace every occurrence of \" with "

For example, if a string = \"name\"

I want the output to be string = "name"

From what I have found, redshift does not allow the existence of a single backslash, and automatically converts it to a double backslash, but that is not happening in this case.

I have tried to use the REPLACE() with REPLACE( string, '\"', '"' ) but it did not have any effect. Can the string being a JSON string have any bearing on the function of REPLACE()?

I have been trying to use regexp_replace but maybe I am not using the right regular expression, hence I am not able to solve the problem.

Solution

  • REPLACE( string, '\\"', '"' ) seems works in this situation. I am guessing its because redshift doesn't allow a single backslash, but converts them to double backslash.

    So, even though the string looked like \"name\" it was probably stored as \\"name\\" and hence putting a single backslash in the replace was not working.

    EDIT: please read Bill's explanation in the comment below this reply