Special Characters in Teradata

Database
Enthusiast

Re: Special Characters in Teradata

@dieter:

Thanks a lot dieter.After installing that UDF function above solution is working.Really thanks a lot for your help.

Regards,

Rahul

Enthusiast

Re: Special Characters in Teradata

I'm not so fortunate.

I installed the UDF, tested it using the samples in the btq file and it works fine,

but when I try to use it to detect invalid characters using:

where CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0

The columns in my table that I've inserted invalid characters (%*~^) for instance are not returned.

Any Idea what I may be missing?

13.10.06

Junior Contributor

Re: Special Characters in Teradata

oTRANSLATE is case sensitive, you need to either add chars 'A' to 'Z' or use 

OTRANSLATE(UPPER(your_column), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')

Dieter

Enthusiast

Re: Special Characters in Teradata

It seems like there has to be something between ''. At least for me, it doesn't work if it's just an empty string. Wonder why...

This gives :  566

SELECT OTRANSLATE('abcd566', 'abcdefghijklmnopqrstuvwxyz',' ') (VARCHAR(10) )

This on the other hand gives null:

SELECT OTRANSLATE('abcd566', 'abcdefghijklmnopqrstuvwxyz','') (VARCHAR(10) )
Junior Contributor

Re: Special Characters in Teradata

Hi Thomas,

your not using the built-in TD14 oTranslate function :-)

It must be a custom implementation which exactly mimics Oracle's empty Varchar equals NULL "feature".

The Oracle way to avoid this is adding a dummy character like:

SELECT OTRANSLATE('abcd566', '.abcdefghijklmnopqrstuvwxyz','.') (VARCHAR(10) )

Dieter

Highlighted
Enthusiast

Re: Special Characters in Teradata

Thanks Dieter. Now I recall, that you might have told me that already in the past... Guess I have either a déjà vu, or a memory leak :-)

Not applicable

Re: Special Characters in Teradata

Hi,

I am using otranslate as below syntax and it gives me error as 6709: The string contains untranstable character.

otranslate(Coalesce(Contractor_name,''),':',' ') new_name

Can anyone help?

Thank you!

Enthusiast

Re: Special Characters in Teradata

Otranslate is an existing function in teradata. Usually DBA uses special schema for all UD functions like 'CRM_UD_FUNCTION.OTRANSLATE()'. Please confirm with DBA for schema & it's access. Function will surely be implemented by DBA.