Teradata中使用strtok函数解析数据时识别异常数据求助

问题背景 #

执行Teradata查询时触发Failure 6706 The string contains an untranslatable character错误，需定位导致姓名拆分逻辑失效的异常PERSON_NAME记录。

原查询语句 #

SELECT 
       PERSON_NAME
       ,Trim(OReplace(PERSON_NAME,StrTok(PERSON_NAME,' ',1),'')) AS AGENT_LAST_NAME
       ,StrTok(PERSON_NAME,' ',1) AS AGENT_FIRST_NAME
FROM TABLENAME 
WHERE CAST(RECORD_START_TS AS DATE) = '2022-11-01';

期望输出格式 #

PERSON_NAME                  AGENT_LAST_NAME           AGENT_FIRST_NAME
           Abraham Gomezfitzgerald      Gomezfitzgerald           Abraham
           Adam Tregoning               Tregoning                 Adam
           Ajiel Marino                 Marino                    Ajiel
           Alexander Ford III           Ford III                  Alexander
           Fernanda Garvey Hernandez    Garvey Hernandez          Fernanda

排查与解决步骤 #

1. 定位异常数据 #

使用TRANSLATE_CHK函数快速筛选含不可转译字符的记录：

SELECT PERSON_NAME
FROM TABLENAME 
WHERE CAST(RECORD_START_TS AS DATE) = '2022-11-01'
  AND TRANSLATE_CHK(PERSON_NAME USING LATIN_TO_UNICODE) <> 0;

也可通过正则匹配非预期字符：

SELECT PERSON_NAME
FROM TABLENAME 
WHERE CAST(RECORD_START_TS AS DATE) = '2022-11-01'
  AND PERSON_NAME LIKE '%[^a-zA-Z0-9 ]%' ESCAPE '\';

2. 修正查询逻辑 #

清理不可转译字符后再执行姓名拆分：

SELECT 
       PERSON_NAME
       ,Trim(OReplace(
           TRANSLATE(PERSON_NAME, '', '¬`~!@#$%^&*()_+{}[]|\\:;"''<>,.?/'),
           StrTok(TRANSLATE(PERSON_NAME, '', '¬`~!@#$%^&*()_+{}[]|\\:;"''<>,.?/'),' ',1),
           ''
       )) AS AGENT_LAST_NAME
       ,StrTok(TRANSLATE(PERSON_NAME, '', '¬`~!@#$%^&*()_+{}[]|\\:;"''<>,.?/'),' ',1) AS AGENT_FIRST_NAME
FROM TABLENAME 
WHERE CAST(RECORD_START_TS AS DATE) = '2022-11-01';

内容的提问来源于stack exchange，提问作者Debasis Das