|
|
|
Forum Member
 
Group: Forum Members
Last Login: 6/12/2008 6:34:13 AM
Posts: 49,
Visits: 3
|
|
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
Akhil Bansal
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 12/27/2006 5:13:00 PM
Posts: 104,
Visits: 1
|
|
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
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 3/27/2007 5:35:00 AM
Posts: 58,
Visits: 1
|
|
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
That's All Folks Divvy Certified TeraData Master
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/23/2006 4:51:00 AM
Posts: 2,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 6/12/2008 6:34:13 AM
Posts: 49,
Visits: 3
|
|
Hi Vinay,
It works fine. thanks.
But what do you mean by crude form ?
Akhil Bansal
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
Akhil,
Crude as "Can be improved further"
Vinay
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/22/2008 3:48:36 AM
Posts: 173,
Visits: 5
|
|
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;
|
|
| |