Q: SUB Special Char needs to be removed from Column in Teradata Need to remove SUB Special Char from Column in Teradata, Have tried using below replace commands but it's not working.
regexp_replace(column, X'1A', '', 1, 0, 'i')
OREPLACE(Column, X'1A' , ' ')

Have tried to remove it with alphanumeric value but some other char are getting removed with it, need to remove only that SUB char from the string.
 regexp_replace(column, '[^0-9a-z]', '', 1, 0, 'i')


A: Handling the error character is a bit tricky in Teradata, any explicit or implicit conversion results in

6706 The string contains an untranslatable character.

If your input column is LATIN you must ensure that all parameters for  oTranslate are LATIN to avoid conversion. If the input is UNICODE all parameters must be UNICODE.
CREATE VOLATILE TABLE vt
 (
   col_latin   VARCHAR(20) CHARACTER SET Latin
  ,col_unicode VARCHAR(20) CHARACTER SET Unicode
 ) ON COMMIT PRESERVE ROWS;
  
INSERT INTO vt
 (
   'hello' || Chr(26) || 'world'
  ,Translate('hello' || Chr(26) || 'world' USING latin_to_unicode WITH ERROR)
 );
 
SELECT 
   OTranslate(col_unicode -- UNICODE
             ,Chr(26)     -- LATIN
             ,' '         -- UNICODE
             )
FROM vt; 
-- fails
             
SELECT
   OTranslate(col_latin   -- LATIN
             ,Chr(26)     -- LATIN
             ,' '         -- UNICODE
             )
FROM vt;
-- fails

SELECT 
   OTranslate(col_unicode -- UNICODE
             ,Translate(Chr(26) USING latin_to_unicode WITH ERROR) -- UNICODE
             ,' '         -- UNICODE
             )
  ,OTranslate(col_latin   -- LATIN
             ,Chr(26)     -- LATIN
             ,Translate('' USING unicode_to_latin) -- LATIN
             )
FROM vt;
-- returns both 'hello world' successfully

