如何用单个SQL查询截取ADDR1中APT/LOT/UNIT等关键词前的内容？

我明白你的需求——要一次性处理多个关键词，不用UNION这种对大数据不友好的方式，核心就是找到最早出现的目标关键词位置，然后截取到它之前的内容就行。下面给你两种实用的方案，按需选择：

方案一：扩展性最强的CROSS APPLY方式（推荐） #

这种方式最大的好处是以后加新关键词（比如'BLDG'、'ROOM'之类的），直接在列表里加一行就行，维护超方便，而且性能也不错：

SELECT 
    SUBSTRING(ADDR1, 1, 
        COALESCE(
            MIN(CASE WHEN CHARINDEX(keyword, UPPER(ADDR1)) > 0 THEN CHARINDEX(keyword, UPPER(ADDR1)) END),
            LEN(ADDR1) + 1
        ) - 1
    ) AS ADDR1_Trimmed
FROM 
    Address
CROSS APPLY (
    -- 这里放所有需要匹配的关键词，前面加空格避免误匹配单词中间的字符
    VALUES 
        (' APT'), (' LOT'), (' UNIT'), (' SUITE'), (' TRAILER'), (' TRLR')
) AS Keywords(keyword)
GROUP BY ADDR1;

关键部分解释： #

• CROSS APPLY (VALUES (...))：把关键词转换成临时的行集合，相当于给每个地址都匹配一遍所有关键词。
• UPPER(ADDR1) + 大写关键词：实现不区分大小写的匹配，不管地址里是'Unit'、'UNIT'还是'unit'都能抓到。
• MIN(...)：从所有找到的关键词位置里取最小的那个（也就是最早出现的关键词）。
• COALESCE(..., LEN(ADDR1)+1)：如果地址里没有任何关键词，就用地址总长度+1，这样SUBSTRING截取到这个位置减1就是整个地址，不会截断。
• -1：因为CHARINDEX返回的是关键词第一个字符的索引，比如' APT'在地址里从第15位开始，那前面的内容就是前14位，所以要减1。

方案二：SQL Server 2022+ 可用的LEAST函数简化版 #

如果你的SQL Server版本是2022及以上，可以用LEAST函数直接取多个CHARINDEX的最小值，代码更简洁：

SELECT 
    SUBSTRING(ADDR1, 1, 
        ISNULL(
            NULLIF(
                LEAST(
                    NULLIF(CHARINDEX(' APT', UPPER(ADDR1)), 0),
                    NULLIF(CHARINDEX(' LOT', UPPER(ADDR1)), 0),
                    NULLIF(CHARINDEX(' UNIT', UPPER(ADDR1)), 0),
                    NULLIF(CHARINDEX(' SUITE', UPPER(ADDR1)), 0),
                    NULLIF(CHARINDEX(' TRAILER', UPPER(ADDR1)), 0),
                    NULLIF(CHARINDEX(' TRLR', UPPER(ADDR1)), 0)
                ), 0
            ), LEN(ADDR1) + 1
        ) - 1
    ) AS ADDR1_Trimmed
FROM Address;

测试你的示例数据： #

对于你给出的三条记录：

• 6321 24TH AVE APT2 → 截取后得到 6321 24TH AVE
• 2232 S ALLIS ST LOT 4 → 截取后得到 2232 S ALLIS ST
• 824 JENIFER ST Unit 2 → 截取后得到 824 JENIFER ST

完全符合你的期望！

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