using OREPLACE function

Tools & Utilities

using OREPLACE function

Have seen some similar mentions using OREPLACE function but need help getting OREPLACE to work on '1A' values, if possible.

For example with the following test data (output using the TRANSLATE_CHK function):

90210|testdata |23

How to remove the untranslatable character ?--> 

Would like to insert from UNICODE character column on Teradata v2r6.1 to LATIN character column on Teradata v2r6.1

Using Ultraedit HexEdit, it appears that the character  is represented as '1A' and was attempting to use the following:

SELECT ID, OREPLACE(ColA, '1A', '' )

FROM test_tbl

Still receive error stating 6706 the string contains untranslatable character.

Any ideas? Thank you.
5 REPLIES
rgs
Enthusiast

Re: using OREPLACE function

Try:

SELECT ID, OREPLACE(ColA, ‘1A’XCV,’’) FROM test_tbl;

Re: using OREPLACE function

Looks interesting.
(question on the tick marks, does this refer to the key next to the 'enter' key?)

Have tried both below with the following errors:

SELECT ID, OREPLACE(ColA, ‘1A’XCV,’’) FROM test_tbl;
error#3706, expected something between ',' and end of the request.

Modified statement as found on forum to use tick key, found beside enter key:
SELECT ID, OREPLACE(ColA, '1A'XCV,'') FROM test_tbl;
error#6706 , the string conatins untranslateable characters

example of data going after:
2x Payment Correction 

hex representation screen shot:
--screen shot does not paste-- there should be an attachment of the screenshot zip file for this hex representation
Junior Contributor

Re: using OREPLACE function

'1A' is an error character which is used when a character cannot be translated.

Is your example data the result of a TRANSLATE WITH ERROR?
Your screenshot is showing ascii/latin but no unicode chars.

If that ColA is unicode OREPACE will not work, because it's defined with CHARACTER SET LATIN and there will be an automatic translation of ColA to unicode *before* the replace. And you can't use the latin character '1A' because it's the error char:

"Note: 0x1A in LATIN/KANJI1/KANJISJIS and U+FFFD in UNICODE/GRAPHIC are used
internally by Teradata as the error character; therefore, they are unusable as user data. The
user cannot store or retrieve these values through Teradata."

You might find out about the actual non-translatable chars using TRANSLATE_CHK and CHAR2HEXINT.
If it's just a single char you could remove it using SUBSTRING.

Dieter

Re: using OREPLACE function

Dieter,

This column is set up as a UNICODE column in the table.

SQL Assistant was used to generate the example.
Pasted answer into Ultraedit to review HEX values.

Will check if the Substring will help.
Thinking how to get any remaining values after the problem character...will check if this will work.

Thank you.

Re: using OREPLACE function

Can any one Expalin the diffarence bet ween REPLACE and OREPLACE ??