How to replace escape character in Netezza column

Question

I am trying to replace escape character in Netezza column, but it is not properly replacing. Please help me some one on this.

select replace('replaces\tring','\','\\\\');

I need output as replaces\\\\tring. Below is the error message i am getting...

ERROR [42S02] ERROR: Function 'REPLACE(UNKNOWN, UNKNOWN, UNKNOWN)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts

Thanks in advance.

Answer 1

That particular function is part of the "SQL Extensions toolkit" and on our system it is placed in the ADMIN schema of the SQLEXT database. All users have been granted execute access to that schema. Furthermore the database.schema have been placed in the path (the DBA's did it globally, but you can issue a "set PATH=..." in your session if need be)

our path is:

select current_path;

CURRENT_PATH
---------------------------------------------------------------------------------------
SQLEXT.ADMIN,INZA.INZA,NZA.INZA,NZM.INZA,NZMSG.INZA,NZR.INZA,NZRC.INZA,SYNCHDB.ADMIN

and as you can see the SQLEXT is at the beginning...

Answer 2

This is because REPLACE function needs to be installed (which is not by default). There is another function which is called TRANSLATE which can be used in a limited way instead of REPLACE but unfortunately won't fit in your situation.

You can use the below query instead:

SELECT SUBSTRING(x, 1, INSTR(x, '\') - 1) || '\\\\' || SUBSTRING(x, INSTR(x, '\') + LENGTH('\')) FROM 
(SELECT 'replaces\tring' AS x) t

• \ passed to INSTR and LENGTH is the string to be replaced. Note that they occur in three positions.
• \\\\ in the middle is the replacement string.
• replaces\tring is the string to search in.

Check the below example for replace love with like in I love Netezza:

SELECT SUBSTRING(x, 1, INSTR(x, 'love') - 1) || 'like' || SUBSTRING(x, INSTR(x, 'love') + LENGTH('love')) FROM 
(SELECT 'I love Netezza' AS x) t