tr转~为null结合xargs -0生成SQL时出现字段匹配异常

我有一个以~分隔字段的文件，需转换为SQL语句，数据包含空格。尝试用tr '~' '\0000'将分隔符转为空字符，配合xargs -0处理空格，但执行后xargs读取字段错位，生成的SQL不符合预期。

输入数据 #

FA_PRD01_PHX1~EBDT~30-JAN-2023~18~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS~1~1
FA_PRD01_PHX1~EBDT~30-JAN-2023~08~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLSNEW~1~1
FA_PRD01_PHX1~EBDT~23-JAN-2023~18~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS~1~1
FA_PRD01_PHX1~EBDT~23-JAN-2023~08~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLSNEW~1~1
FA_PRD01_PHX1~EBDT~13-MAR-2023~04~JobDefinition://company/apps/ess/custom/shared/Apps_Reports/Certifications and Competencies/ALLCERTRPT~1~1
FA_PRD01_PHX1~EBDT~13-FEB-2023~18~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS~1~1
FA_PRD01_PHX1~EBDT~13-FEB-2023~08~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLSNEW~1~1
FA_PRD01_PHX1~EBDT~13-FEB-2023~05~JobDefinition://company/apps/ess/custom/shared/Apps_Reports/Certifications and Competencies/ALLCERTRPT~1~1
FA_PRD01_PHX1~EBDT~06-FEB-2023~18~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS~1~1
FA_PRD01_PHX1~EBDT~06-FEB-2023~08~JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLSNEW~1~1

执行命令 #

cat data.txt | tr '~' '\0000' | 
   xargs -0 -n 7 printf "insert into ESS_SYSTEM_ERROR_METRICS  values('%s','%s',to_date('%s','DD-MM-YYYY'),%s,'%s',%s,%s);\n"

预期输出 #

insert into ESS_SYSTEM_ERROR_METRICS  values('FA_PRD01_PHX1','EBDT',to_date('30-JAN-2023','DD-MM-YYYY'),18,'JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS',1,1);

实际输出 #

insert into ESS_SYSTEM_ERROR_METRICS  values('FA_PRD01_PHX1','EBDT',to_date('30-JAN-2023','DD-MM-YYYY'),18,'JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLS',1,1
FA_PRD01_PHX1);
insert into ESS_SYSTEM_ERROR_METRICS  values('EBDT','30-JAN-2023',to_date('08','DD-MM-YYYY'),JobDefinition://company/apps/ess/custom/Shared Folders/Apps_Reports/APPSSALESFORCETIMEDTLSNEW,'1',1
FA_PRD01_PHX1,EBDT);

问题原因 #

你的命令中，tr '~' '\0000'仅将~替换为空字符，但每行末尾的换行符未被处理。xargs -0会将换行符当作字段内容的一部分，而非分隔符，导致每一行的最后一个字段（比如第一行的1）会和下一行的第一个字段（FA_PRD01_PHX1）合并成一个字段传入printf，破坏了-n 7的分组逻辑，最终造成字段错位。

解决方案 #

方案1：修正tr和xargs命令

将换行符也替换为空字符，确保所有字段（包括行与行之间的字段）都以空字符分隔：

cat data.txt | tr '~\n' '\0\0' | 
   xargs -0 -n 7 printf "insert into ESS_SYSTEM_ERROR_METRICS  values('%s','%s',to_date('%s','DD-MM-YYYY'),%s,'%s',%s,%s);\n"

tr '~\n' '\0\0'同时把~和换行符都转为空字符，让xargs能正确识别每个独立字段。

方案2：使用awk（更推荐）

awk天生支持按自定义分隔符分割字段，无需处理空字符和xargs的分组问题，逻辑更直观可靠：

awk -F'~' '{
    printf "insert into ESS_SYSTEM_ERROR_METRICS values('\''%s'\'','\''%s'\'',''to_date('\''%s'\'','\''DD-MM-YYYY'\'')'',%s,'\''%s'\'',''%s'\'',''%s'\'');\n", 
    $1, $2, $3, $4, $5, $6, $7
}' data.txt

• -F'~'指定以~作为字段分隔符
• 直接通过$1到$7引用每行的7个字段
• 用printf格式化输出SQL语句，自动处理字段内的空格

内容的提问来源于stack exchange，提问作者Christian Bongiorno