Thanks a lot dieter.After installing that UDF function above solution is working.Really thanks a lot for your help.
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?
oTRANSLATE is case sensitive, you need to either add chars 'A' to 'Z' or use
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) )
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) )
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 :-)
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?
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.