String Search and Replace.

Analytics

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.

12 REPLIES
Enthusiast

Re: String Search and Replace.

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

Re: String Search and Replace.

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.
Enthusiast

Re: String Search and Replace.

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
Enthusiast

Re: String Search and Replace.

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

Re: String Search and Replace.

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 Teradata.com.
Format is:

oreplace(col1,'FRED','Bill')

changes all occurence of FRED to Bill in col1.
Fan

Re: String Search and Replace.

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.

Enthusiast

Re: String Search and Replace.

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

Re: String Search and Replace.

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

Enthusiast

Re: String Search and Replace.

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