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:
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.
The following posts would provide you the material of relevant interest:
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'
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.