All Forums Database
02 Jan 2006
String Replacement

Hi,Is there is a way to replace a particular in a column with another character ?Support a Column values likeYahoo and HotMailHotMail and YahooYahoo and GMailGMail and HotMailI would like to replace "and" word with any other word like 'or'.Thanks and Regards

khab 6 posts Joined 01/05
02 Jan 2006

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

XTUPIE 42 posts Joined 11/05
03 Jan 2006

HiI 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

Regards
Divvy

surmik 2 posts Joined 12/05
03 Jan 2006

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

BBR2 96 posts Joined 12/04
03 Jan 2006

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

04 Jan 2006

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

BBR2 96 posts Joined 12/04
04 Jan 2006

Akhil,Crude as "Can be improved further"Vinay

DGiabbai 47 posts Joined 07/04
04 Jan 2006

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;

09 Jan 2006

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

You must sign in to leave a comment.