String Replacement

Database
Enthusiast

String Replacement

Hi,

Is there is a way to replace a particular in a column with another character ?

Support a Column values like

Yahoo and HotMail
HotMail and Yahoo
Yahoo and GMail
GMail and HotMail

I would like to replace "and" word with any other word like 'or'.

Thanks and Regards
8 REPLIES
Enthusiast

Re: String Replacement

Hi Akhil,
There is no such finction avaliable in TD, but you can make your work easy with UDF. There is a Oracle UDF which can do this job. I guess you can find the UDFs at www.teradata.com

-Cheers
Enthusiast

Re: String Replacement

Hi

I agree, I have tried using a normal Update

"update dba.testing set linea = 'or' where linea like '%and%' ;"

But one gets a duplicate error. You can do this with a program or a UDF.

Divvy
Fan

Re: String Replacement

Or you can:

create table your_table2
as your_table
with no data
;

insert into your_table2
select substring(your_field FROM 1 FOR INDEX(your_field,' and') -1) || ' or ' || substring(your_field FROM INDEX(your_field,'and ')+4)
from your_table;

drop table your_table;

rename your_table2 to your_table;

Marek
Enthusiast

Re: String Replacement

I came up w/t a Macro that can accomplish this.

You may want to use the same logic in your procedure or SQL.

REPLACE MACRO SYSDBA.REPLACE_STR ( SEARCH_STR VARCHAR(400), CHNG_STR VARCHAR(400))
AS
(
SEL
ORIGINAL_STRING
,REPLACED_STRING
FROM
(SEL
NME AS ORIGINAL_STRING
,POSITION(:SEARCH_STR IN NME) AS START_POINT
,CHARACTER_LENGTH(TRIM(NME)) AS MAX_LEN
,CHARACTER_LENGTH(:SEARCH_STR) AS NO_CHAR
,CASE WHEN START_POINT > 1
THEN SUBSTR(NME, 1 , (START_POINT-1)) || :CHNG_STR
|| SUBSTR(NME, (START_POINT+NO_CHAR), MAX_LEN)
WHEN START_POINT = 1
THEN :CHNG_STR || SUBSTR(NME, NO_CHAR+1, MAX_LEN)
WHEN MAX_LEN = START_POINT + NO_CHAR
THEN SUBSTR(NME, 1 ,(START_POINT-1)) || :CHNG_STR END AS REPLACED_STRING
FROM SYSDBA.TEST ) AS T ;-) ;

I have created a test table and you can execute the is macro as

EXEC SYSDBA.REPLACE_STR ('VINAY','DBA')
It seems to work but is in crude form.

Vinay

Enthusiast

Re: String Replacement

Hi Vinay,

It works fine. thanks.
But what do you mean by crude form ?

Enthusiast

Re: String Replacement

Akhil,

Crude as "Can be improved further"

Vinay
Enthusiast

Re: String Replacement

The following stored procedure performs the task of a Replace function for character data.
In this form it can be used only from within another stored procedure.

REPLACE PROCEDURE SysDBA.StringReplace
(
IN TargetString VARCHAR(30000) -- 33998
, IN SearchString VARCHAR(255)
, IN ReplaceString VARCHAR(255)
, OUT OutString VARCHAR(30000)
)
-----------------------------------------------------------------
-- INSTALL REQUIREMENTS:
-- 1) GRANT ALL ON "%TDUser%" TO "%TDUser%" WITH GRANT OPTION
-- 2) C++ compiler, if running the TD Demo
-- HOW TO INSTALL:
-- 1) Logon to teradata using the %TDUser% login
-- 2) Run the "REPLACE PROCEDURE" SQL statement
-----------------------------------------------------------------
-- NAME : StringReplace
-- DESCRIPTION: Collects statistics on both primary and secondary
-- indices, and on columns.
-- PARAMETERS :
-- 1) TargetString
-- 2) SearchString
-- 3) ReplaceString
-- REMARKS : Can only be used from another Stored Procedure
-- EXAMPLES :
--
-- DECLARE outStr VARCHAR(33998);
-- CALL testload.Replace('Hello, old world!', 'old ', '', OutString)
--
-- and get the output in the OutString variable:
--
-- outStr = 'Hello, world!'
--
-----------------------------------------------------------------
BEGIN

DECLARE sOutString VARCHAR(30000);
DECLARE sTmpSearchStr VARCHAR(30000);
DECLARE iTargetStrLen INTEGER;
DECLARE iSearchStrLen INTEGER;
DECLARE iSearchIndex INTEGER;

SET sOutString = '';
SET iTargetStrLen = CHAR_LENGTH( TargetString );
SET iSearchStrLen = CHAR_LENGTH( SearchString );

IF (iSearchStrLen <= 0) THEN
SET OutString = TargetString;
ELSEIF (iTargetStrLen <= 0) THEN
SET OutString = '';
ELSEIF (iSearchStrLen > iTargetStrLen) THEN
SET OutString = TargetString;
ELSE

SET sOutString = '';
SET sTmpSearchStr = TargetString;
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

WHILE iSearchIndex <> 0 DO

SET sOutString = sOutString ||
SUBSTR(
sTmpSearchStr
, 1
, iSearchIndex - 1
)
|| ReplaceString ;
SET sTmpSearchStr = SUBSTR(
sTmpSearchStr
, iSearchIndex + iSearchStrLen
, CHAR_LENGTH(sTmpSearchStr) - (iSearchIndex + iSearchStrLen) + 1
);
SET iSearchIndex = POSITION( SearchString IN sTmpSearchStr );

END WHILE;

SET sOutString = sOutString
|| SUBSTR (
sTmpSearchStr
, 1
, CHAR_LENGTH(TRIM(TRAILING FROM sTmpSearchStr))
);
SET OutString = sOutString;

END IF;

END;
Enthusiast

Re: String Replacement

Hi Daniel

Thanks for this stored procedure. But is there is a way that I can use this in Select statement ?

Regards