下面是SQL语句:
SELECT
CAST(S_TP2_Customer.CUST_NUMBER as Varchar(15)) as Cust,
S_TP2_Siebel.*,
S_TP2_CompanyInfo.COMPANY_NUMBER,
cast(NULLIF(
otranslate(
case
when position('!' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('@' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('#' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('$' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('%' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('^' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('&' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('*' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('(' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position(')' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('-' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('_' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('+' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('=' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('{' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('[' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('}' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position(']' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('|' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('\' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position(':' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position(';' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('"' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('''' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('<' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position(',' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('>' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('.' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('?' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
when position('/' in S_TP2_CompanyInfo.COMPANY_NUMBER) > 0 then NULL
else S_TP2_CompanyInfo.COMPANY_NUMBER
end, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ', ''
), '')
as varchar(30)) as CRN_Initial,
otranslate(cast(CRN_Initial as varchar(30)), '0', '') as Testa
FROM EDW1TST_StagePerm.S_TP2_Customer S_TP2_Customer