String Replacements in Teradata Sql

Database

String Replacements in Teradata Sql

Hi,

I am new to Teradata, learning SQL queries and I have a query regarding string replacement.

for ex. if i have a table data as below:

Id, name

1  john xyz

2  john abcd

3  john abded

I want to keep the name 'john' as it is and replace the letters after space with a new name like 'Thomas' or  'steve', then how do I write a query for the same.

3 REPLIES
Enthusiast

Re: String Replacements in Teradata Sql

Teradata doesn't have a REPLACE function like Oracle does.

If you are looking for some complex replace functionality like replacing multiple instances of the same set of characters in the given string then you will have to use UDF which you can found in the downloads section.

If you just want to replace the characters after the first occurence of space with some other name then this SQL will do the job...

SELECT ID, NAME, SUBSTRING(NAME FROM 1 FOR POSITION(' ' IN NAME)-1) || ' THOMAS'
FROM <<TABLENAME>>

WAQ
Enthusiast

Re: String Replacements in Teradata Sql

Just to add one more thing to what Qaiser suggested, you can also use recursive SQL to replace more than one occurances of a string.

By the way in your example, you are not replacing one particluar string rather the string is changing in every value. So the query given by Qaiser will serve your purpose.