SQL Server 2019外层查询CROSS APPLY STRING_SPLIT失效问题

你的外层CROSS APPLY STRING_SPLIT没生效，核心问题是你没真正使用拆分后的结果：

• 外层SELECT列表里引用的还是子查询C中的原始LOCATOR字段，不是STRING_SPLIT输出的拆分后值
• WHERE子句的过滤条件也只针对原始LOCATOR，没对拆分后的结果做校验

修改后的完整SQL #

select
    DISTINCT
    c.partynumber
    ,ELECTRONICADDRESSID
    ,COUNTRYREGIONCODE
    ,DESCRIPTION
    ,ISINSTANTMESSAGE
    ,ISMOBILEPHONE
    ,CASE 
        when isnumeric(PURPOSE) = 1
        then 
            case 
                when purpose = 1 
                then 'Yes'
                else 'No'
                end
        else 
            'No'
    end as ISPRIMARY
    ,LOCATIONID
    ,LOCATOR.value AS LOCATOR -- 改用拆分后的字段
    ,LOCATOREXTENSION
    ,case 
        when isnumeric(PURPOSE) = 1
        then 
            case 
                when purpose = 1 
                then 'Business;Invoice;Remit to;Statement;Purchase Order'
                else
                    'Business'
                end
        else 
            purpose
        end 
            as purpose
    ,TYPE
from 
(
--vendors
    select 
        vpm.PARTYNUMBER PARTYNUMBER
        ,'auto generated'    ELECTRONICADDRESSID
        ,'' COUNTRYREGIONCODE
        ,'Email address' DESCRIPTION
        ,'' ISINSTANTMESSAGE
        ,'' ISMOBILEPHONE
        ,'' ISPRIMARY
        ,'' ISPRIVATE
        ,'auto generated' LOCATIONID
        ,(SELECT nicholas.dbo.name_and_address_master.na_name
        FROM   nicholas.dbo.name_and_address_master
        WHERE  nicholas.dbo.name_and_address_master.accountcode = cm.cre_accountcode
            AND nicholas.dbo.name_and_address_master.na_type = 'E')   LOCATOR
        ,'' LOCATOREXTENSION
        ,cast(row_number() over (partition by vpm.partynumber order by vpm.partynumber) as varchar(max)) PURPOSE
        , 'Email Address'   as  TYPE
    from 
        nicholas.dbo.cre_master cm
    left join
        mapping.dbo.vendor_party_mapping vpm on vpm.xi_code = cm.cre_accountcode
    where
        (SELECT nicholas.dbo.name_and_address_master.na_name
        FROM   nicholas.dbo.name_and_address_master
        WHERE  nicholas.dbo.name_and_address_master.accountcode = cm.cre_accountcode
            AND nicholas.dbo.name_and_address_master.na_type = 'E') != ''
    and
        vpm.PARTYNUMBER is not null
    UNION ALL
    --customers
    select 
        (select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = accountcode)    PARTYNUMBER
        ,'auto generated'    ELECTRONICADDRESSID
        ,'' COUNTRYREGIONCODE
        ,'Email address' DESCRIPTION
        ,'' ISINSTANTMESSAGE
        ,'' ISMOBILEPHONE
        ,'' ISPRIMARY
        ,'' ISPRIVATE
        ,'auto generated' LOCATIONID
        ,(SELECT top 1 CONCAT(naam.na_name, naam.na_company)
            FROM   nicholas.dbo.name_and_address_master naam
            WHERE  naam.accountcode = dm.accountcode
                   AND naam.na_type = 'E')  LOCATOR
        ,'' LOCATOREXTENSION
        ,cast(row_number() over (partition by dm.accountcode order by dm.accountcode) as varchar(max)) PURPOSE
        , 'Email Address'   as  TYPE
    from 
        nicholas.dbo.deb_master dm 
    left join 
        mapping.dbo.customer_id_master cim on cim.xi_code = dm.accountcode
    where
        dm.dr_cust_type != '..'
    and
        exists (select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = accountcode)
    and
        (SELECT top 1 CONCAT(naam.na_name, naam.na_company)
            FROM   nicholas.dbo.name_and_address_master naam
            WHERE  naam.accountcode = dm.accountcode
                   AND naam.na_type = 'E') != ''
    UNION ALL
    --true forms
    select 
        (select top 1 partynumber from mapping.dbo.customer_party_mapping where xi_code = b.XiAccountCode)    PARTYNUMBER
        ,'auto generated'    ELECTRONICADDRESSID
        ,'' COUNTRYREGIONCODE
        ,b.DocumentType DESCRIPTION
        ,'' ISINSTANTMESSAGE
        , '' ISMOBILEPHONE
        ,'' ISPRIMARY
        ,'' ISPRIVATE
        , 'auto generated' LOCATIONID
        ,b.value LOCATOR
        ,'' LOCATOREXTENSION
        ,case
            when b.DocumentType = 'Invoice'
            then 'Business;Invoice'
            when b.DocumentType = 'Customer Statement'
            then 'Business;Statement'
            when b.DocumentType = ' Remittance'
            then 'Business;Remit To'
            else 'Business'
            end as PURPOSE
        , 'Email Address'   as  TYPE
    from 
    (SELECT *
    FROM (SELECT 
    tdm.tdm_type AS CustomerOrVendor, 
    tdm.contact_type,
    tdm.tdm_account AS XiAccountCode, 
    CASE
        WHEN tdm.tdm_document = 'CS'
        THEN 'Customer Statement'
        WHEN tdm.tdm_document = 'PO'
        THEN 'Purchase Order'
        WHEN tdm.tdm_document = 'EFT'
        THEN 'Remittance'
        WHEN tdm.tdm_document = 'CR'
        THEN 'Customer Rebate'
        WHEN tdm.tdm_document = 'CN'
        THEN 'Credit Note'
        WHEN tdm.tdm_document = 'PL'
        THEN 'POS Layby'
        WHEN tdm.tdm_document = 'INV'
        THEN 'Invoice'
        WHEN tdm.tdm_document = 'POQ'
        THEN 'Purchase Quotations'
        ELSE 'ERROR'
    END AS          DocumentType,
    CASE
        WHEN tdm.contact_type = 'A'
        THEN (ISNULL((SELECT CONCAT(nicholas.dbo.name_and_address_master.na_name, nicholas.dbo.name_and_address_master.na_company)
            FROM   nicholas.dbo.name_and_address_master 
            WHERE  nicholas.dbo.name_and_address_master.accountcode = tdm.tdm_account
                   AND nicholas.dbo.name_and_address_master.na_type = 'E'),''))
        WHEN tdm.contact_type = 'M'
        THEN tdm.tdm_address
        ELSE 'ERROR'
    END AS EmailAddress
    FROM  nicholas.dbo.trueform_document_map tdm) a
    CROSS APPLY STRING_SPLIT(a.EmailAddress, ';') as LOCATOR 
    WHERE a.EmailAddress != '' and a.EmailAddress like '%@%'
    ) b
) C
CROSS APPLY STRING_SPLIT(c.LOCATOR, ';') as LOCATOR 
where LOCATOR.value like '%@%' -- 改用拆分后的字段过滤
and LOCATOR.value like '%[A-Z0-9][@][A-Z0-9]%[.][A-Z0-9]%' -- 改用拆分后的字段过滤
and (c.PARTYNUMBER is not null and c.partynumber != '')
order by c.PARTYNUMBER

关键修改点 #

1. SELECT列表替换字段：把LOCATOR改成LOCATOR.value，这样输出的是拆分后的单个邮箱地址，而非原始带分号的字符串
2. WHERE条件调整：将过滤c.Locator的条件，改成过滤拆分后的LOCATOR.value，确保只保留有效的单个邮箱
3. 保留DISTINCT去重，避免拆分后出现重复行

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