如何用SQL基于查找表清理列中混杂的单位文本？

需求背景 #

从Excel导入了ImportedTable数据表，表中Text列的内容混杂了各种单位。需要借助包含约20种单位的LookupTable，通过**SELECT语句（不修改原表）**清理每条记录的Text列，移除所有匹配的单位值，得到干净文本。ImportedTable约有2000条记录。

示例数据 #

LookupTable（单位对照表） #

Id  | Name
1   | % time operational
2   | KGal
3   | Gallons
...

ImportedTable（待清理表） #

Id  | Text
1   | SomeText here % time operational
2   | 500 KGal
3   | 1.05 Gallons
4   | 105,000
5   | TestTextKGal
...

期望结果 #

'SomeText here'
'500'
'1.05'
'105,000'
'TestText'

尝试过的无效SQL #

SELECT LTRIM(RTRIM(REPLACE([Text], (SELECT DISTINCT [Name] FROM LookupTable), '')))
FROM ImportedTable

可行解决方案 #

你的问题出在子查询(SELECT DISTINCT [Name] FROM LookupTable)只会返回单个单位值（取决于数据库返回规则），所以REPLACE只能替换一种单位，无法批量处理所有单位。以下是几种可行方案：

方案1：嵌套REPLACE（适合单位数量少的场景） #

直接嵌套所有单位的REPLACE操作，逻辑简单直观：

SELECT 
    LTRIM(RTRIM(
        REPLACE(
            REPLACE(
                REPLACE([Text], '% time operational', ''),
                'KGal', ''
            ),
            'Gallons', ''
            -- 按此格式继续添加剩余单位的REPLACE语句
        )
    )) AS CleanText
FROM ImportedTable

方案2：递归CTE（适合单位数量多或需动态扩展的场景） #

通过递归循环逐个替换所有单位：

WITH RecursiveReplace AS (
    -- 初始步骤：关联原始文本与所有单位
    SELECT 
        it.Id,
        it.Text AS OriginalText,
        it.Text AS CleanText,
        lt.Name AS UnitToRemove,
        1 AS ReplaceStep
    FROM ImportedTable it
    CROSS JOIN LookupTable lt
    
    UNION ALL
    
    -- 递归替换：每次处理一个未替换过的单位
    SELECT 
        rr.Id,
        rr.OriginalText,
        REPLACE(rr.CleanText, rr.UnitToRemove, '') AS CleanText,
        lt.Name AS UnitToRemove,
        rr.ReplaceStep + 1 AS ReplaceStep
    FROM RecursiveReplace rr
    JOIN LookupTable lt ON lt.Id > (SELECT MAX(Id) FROM LookupTable WHERE Name = rr.UnitToRemove)
)
-- 提取每个记录最终替换后的结果，去除首尾空格
SELECT 
    DISTINCT Id,
    LTRIM(RTRIM(CleanText)) AS CleanText
FROM RecursiveReplace
WHERE ReplaceStep = (SELECT COUNT(*) FROM LookupTable)
ORDER BY Id;

方案3：动态SQL（SQL Server环境适用） #

通过字符串聚合自动生成替换逻辑，适合单位数量较多的场景：

DECLARE @ReplaceSQL NVARCHAR(MAX)
SET @ReplaceSQL = 'SELECT LTRIM(RTRIM(' + 
    STUFF((
        SELECT ', REPLACE(' 
        FROM LookupTable
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + 
    '[Text]' + 
    REPLICATE(')', (SELECT COUNT(*) FROM LookupTable)) + 
    ') AS CleanText FROM ImportedTable'

EXEC sp_executesql @ReplaceSQL

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