All Forums Analytics
CapsuleCorpJX 2 posts Joined 09/04
16 Oct 2007
String Search and Replace.

Is there an SQL update statement that can scan through the strings of a column, look for a particular word and replace it with another word? The column has sentences in it, and I want to replace just one word while leaving the rest of the sentence intact.

leo.issac 184 posts Joined 07/06
26 Oct 2007

Teradata does not support this kind of translation.However you can write a udf or use other programming languages to perform this translation

joedsilva 505 posts Joined 07/05
27 Oct 2007

you can make use of a combination of postition, substring, character_length functions and the concatenation operator (||) to accomplish this. The syntax and details are available in the functions and operators manual.

littldo 7 posts Joined 07/04
22 Dec 2009

how come teradata doesn't improve it's function set. We're moving brands and this is one reason I support it. 30 years, and it still doesn't have decent string capability

littldo 7 posts Joined 07/04
22 Dec 2009

how would this be done using functions for 'n' occurances of the string?

Jimm 298 posts Joined 09/07
23 Dec 2009

If you are looking to replace a single occurence of a string in a field, Index (or ANSI Position) statement will do it fine.For multiple occurences, you can get the oreplace UDF from is:oreplace(col1,'FRED','Bill')changes all occurence of FRED to Bill in col1.

PCPaul 4 posts Joined 03/08
04 Jan 2010

Teradata does not support this kind of translation.We can achieve this if you are very good at shell programming.Export data form table to flat file and then made what are the changes you need using shell scripting and again load the data to the table. This is one way.

Paul Johnson 12 posts Joined 01/10
03 Feb 2010

Or use Cobol on the mainframe or C on Unix, which we have had to do many, many times...

jainayush007 50 posts Joined 03/11
07 Oct 2011

I could create a recursive few to handle this... :)

Jim Chapman 449 posts Joined 09/04
10 Oct 2011

Release 14.0 provides the OREPLACE function as a system function.  It also provides the REGEXP_REPLACE function.

Adeel Chaudhry 773 posts Joined 04/08
11 Oct 2011

And we are for sure working on improving the function set!

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.