Oracle "REPLACE" function equivalant function in Teradata

Database
Enthusiast

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?

7 REPLIES
Enthusiast

Re: Oracle "REPLACE" function equivalant function in Teradata

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 col1
end

A working SQL statement...

select
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 col1
end as col1_replaced
from
(
select
'this is the replacement xyz string' as col1
) a
;

Enthusiast

Re: Oracle "REPLACE" function equivalant function in Teradata

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

http://www.teradata.com/teradataForum/shwmessage.aspx?forumid=9&messageid=3272#bm3297

Vinay
Teradata Employee

Re: Oracle "REPLACE" function equivalant function in Teradata

use the OREPLACE function in Teradada.

good look

Re: Oracle "REPLACE" function equivalant function in Teradata

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

BEGIN

DECLARE lv_flag CHAR(1) DEFAULT 'Y';

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;

END WHILE;

SET out_target_str = lv_process_str;

END;

Hope this might be useful.

Re: Oracle "REPLACE" function equivalant function in Teradata

Hi All,

select APPUTIL_USER.OREPLACEUC('ARUP RATAN BANERJEE','A','C');

CRUP RCTCN BCNERJEE

Thanks

Arup

Enthusiast

Re: Oracle "REPLACE" function equivalant function in Teradata

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

Re: Oracle "REPLACE" function equivalant function in Teradata

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.

Example:

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–

 

CREATE SET TABLE test.test_table ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      column1 INTEGER,

      column2 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(50)')

PRIMARY INDEX ( column1 );

 

Insert a test record – Notice the |’s -

 

INSERT INTO test.test_table

(column1, column2)

VALUES               

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