Search code examples
apache-drill

Multiple string replace in apache drill using REGEXP_REPLACE sql method

I am using drill sql query on json data. But one of my json field seems to have few characters e.g. '\n' & '^' etc which I want to replace on the fly.

Currently, I am calling REGEXP_REPLACE twice as below -

SELECT REGEXP_REPLACE(REGEXP_REPLACE('aaaa\nbbbb^cccc', '\\n', ' '), '\^', ' ') FROM (VALUES(1));

How can I do that using REGEXP_REPLACE method only once?

Solution

  • The below must work -

    SELECT REGEXP_REPLACE('aaaa\nbbbb^cccc', '\\n|\^', ' ') FROM (VALUES(1));

    But note that in this case, the character you are replacing with, will be same for all. If you have to replace with different characters then you would need to go with your approach only as below -

    SELECT REGEXP_REPLACE(REGEXP_REPLACE('aaaa\nbbbb^cccc', '\\n', 'X'), '\^', 'Y') FROM (VALUES(1));