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.
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
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...
