Teradata Equivalent of oracle Translate function

Tools & Utilities
Enthusiast

Teradata Equivalent of oracle Translate function

Hi,

I like to know how to translate the following Oracle function into Teradata.

select translate(fieldname, '^', '#')

Thanks in advance,
JMM
4 REPLIES
Enthusiast

Re: Teradata Equivalent of oracle Translate function

If your character is in a fixed position, you can use Substring function to manipulate that. If not, you may use Substring and Index functions together to manipulate that.
Enthusiast

Re: Teradata Equivalent of oracle Translate function

Thanks for your reply. But In my field, there are more than one( ^) character.

Thnx,
JMM
Enthusiast

Re: Teradata Equivalent of oracle Translate function

suppose the column length is 3 then
then following. According to the length of the column you need to change the sql.

select
case when substr(column_name,1,1)='^' then '#'
else substr(column_name,1,1)
end
||
case when substr(tt_dsc,2,1)='^' then '#'
else substr(column_name,2,1)
end
||
case when substr(column_name,2,1)='^' then '#'
else substr(column_name,2,1)
end

from database_name.table_name
Enthusiast

Re: Teradata Equivalent of oracle Translate function

You may use UDF's to accomplish this. There are some pre-defined UDF's for oracle functions available in the Teradata site. You may want to look at that to get some directions:

http://www.teradata.com/t/go.aspx/?id=118786