Teradata Forums / Teradata / Teradata Database / String Replacement / Latest Posts

RE: String Replacement

Akhil — Mon, 09 Jan 2006 11:26:14 GMT

Hi Daniel

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

Regards

RE: String Replacement

Daniel Giabbai — Wed, 04 Jan 2006 11:49:29 GMT

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;

RE: String Replacement

Vinay Bagare — Wed, 04 Jan 2006 08:51:04 GMT

Akhil,

Crude as "Can be improved further"

Vinay

RE: String Replacement

Akhil — Wed, 04 Jan 2006 04:49:29 GMT

Hi Vinay,

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

RE: String Replacement

Vinay Bagare — Tue, 03 Jan 2006 14:43:32 GMT

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

RE: String Replacement

surmik — Tue, 03 Jan 2006 04:28:09 GMT

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

RE: String Replacement

Divvy — Tue, 03 Jan 2006 01:02:46 GMT

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

RE: String Replacement

khab — Mon, 02 Jan 2006 08:12:28 GMT

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

String Replacement

Akhil — Mon, 02 Jan 2006 05:16:02 GMT

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