All Forums Database
trying_to_learn 14 posts Joined 08/06
22 Aug 2006
Oracle "REPLACE" function equivalant function in Teradata

Can any one help me (if possible, with the syntax and an example) to find a equivalent function in Teradata that works like 'REPLACE' function in Oracle?

williamdieter 15 posts Joined 03/06
11 Sep 2006

There is no replace function in Teradata. Depending on the version of Teradata you are using, you might be able to use a UDF (User Defined Function).Otherwise, the SQL below will replace the only the first instance of the string you want to replace.If you want to replace 'xyz' with 'abc' use this:case when position('xyz' in col1) > 0 then substr(col1,1,position('xyz' in col1) - 1) || 'abc' || substr(col1,position('xyz' in col1) + character_length('xyz')) else col1endA working SQL statement...selectcase when position('xyz' in col1) > 0 then substr(col1,1,position('xyz' in col1) - 1) || 'abc' || substr(col1,position('xyz' in col1) + character_length('xyz')) else col1end as col1_replacedfrom ( select 'this is the replacement xyz string' as col1 ) a;

BBR2 96 posts Joined 12/04
11 Sep 2006

You may want to look at few techniques that we discussed in past

Diecorp 14 posts Joined 06/12
14 Sep 2012

use the OREPLACE function in Teradada.
good look

abinash432 1 post Joined 05/13
15 May 2013

Hi All,
In order to overcome this I have created a procedure in my database to replace a particular string with another.
REPLACE PROCEDURE REPLACE (IN in_source_str VARCHAR(4000), IN in_search_str VARCHAR(1000), IN in_replace_str VARCHAR(1000), OUT out_target_str VARCHAR(4000) )

DECLARE lv_process_str VARCHAR(4000);

SET lv_process_str = in_source_str;
WHILE lv_flag='Y' DO
 SET lv_process_str =  SUBSTRING(lv_process_str FROM 1 FOR POSITION(in_search_str IN lv_process_str)-1 ) ||in_replace_str||SUBSTRING(lv_process_str FROM POSITION(in_search_str IN lv_process_str)+1 FOR CHARACTER_LENGTH(lv_process_str)-1);
    IF POSITION(in_search_str IN lv_process_str) = 0 THEN
        SET lv_flag='N';
    END IF;
SET out_target_str = lv_process_str;
SET out_target_str = lv_process_str;

Hope this might be useful.

arup99991 1 post Joined 09/12
16 May 2013

Hi All,

Qaisar Kiani 337 posts Joined 11/05
17 May 2013

Teradata now provides the OREPLACE function bydefault, so why not use that?

varma.geet 1 post Joined 09/13
26 Sep 2013

A recursive implementation to replace multiple occurences of a search string with a replacement string in a column -
For 13.0/13.1; if you have Teradata 14.0 use OREPLACE.
We will try to replace all pipes in a Unix command with a string of our choice.
Steps to simulate: Create a table to hold the unix command. Insert the unix command. Now we are all set up. Run the recursive sql to output the changed command.
Create table–
      column1 INTEGER,
PRIMARY INDEX ( column1 );
Insert a test record – Notice the |’s -
INSERT INTO test.test_table
(column1, column2)
(1, 'wc -l example.txt |  sed -e ''s/^ *//g'' -e ''s/ *$//g'' | awk ''{PRINT $1}''');
This command counts the lines in an example.txt text file, removes the formatting and prints only the number of lines. We need to replace all pipes (2) with 'xxx'; the first one is not a pipe, it is an l (L).
Remove the pipes and replace with ‘xxx’!!!!!
With recursive recur(seq, column2) AS
select 1, column2 from test_table
union all
select seq+1, SUBSTRING(recur.column2 FROM 1 FOR POSITION('|' IN recur.column2)-1 ) || 'xxx' || SUBSTRING(recur.column2 FROM POSITION('|' INrecur.column2)+1 FOR CHARACTER_LENGTH(recur.column2)-1)
from recur, test_table
where  POSITION('|' IN recur.column2) > 0
select top 1 seq, column2
from recur
order by seq desc;
Since we have 2 pipes, it loops twice [3 db round trips in the recursive part]. Teradata 13.1 ONLY supports UNION ALL set operator in recursive queries. 
The example above works for 1 row at a time but can be extended/called to support multiple rows.


You must sign in to leave a comment.