如何连接ID格式不一致的表？解决SQL类型转换失败问题

核心问题 #

你的查询报错是因为Table_2.tid中存在包含非数字字符的字符串（如DTBA234），直接用CAST转换会失败。下面提供几种针对性的解决方案：

方案1：用TRY_CAST避免转换报错（基础版） #

如果只需要关联那些能直接转换为数字的tid（比如带前导零的00123），带非数字前缀的记录自动不关联，可以用TRY_CAST替代CAST——它在转换失败时会返回NULL，不会中断查询：

SELECT *
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2
ON t1.Termid = TRY_CAST(t2.tid AS INT)

方案2：固定前缀替换（精准匹配） #

如果已知tid的前缀只有几种（比如DTBA、EDC），可以先去掉前缀再转换，确保能提取到正确的数字部分：

SELECT *
FROM Table_1 AS t1
LEFT JOIN (
    SELECT 
        *,
        TRY_CAST(
            CASE 
                WHEN tid LIKE 'DTBA%' THEN STUFF(tid, 1, 4, '') -- 去掉DTBA前缀
                WHEN tid LIKE 'EDC%' THEN STUFF(tid, 1, 3, '')  -- 去掉EDC前缀
                ELSE tid
            END AS INT
        ) AS numeric_tid
    FROM Table_2
) AS t2
ON t1.Termid = t2.numeric_tid

方案3：提取所有数字字符（通用版） #

如果tid的非数字前缀不固定，或者非数字字符穿插在数字中，需要提取所有数字字符组成纯数字串再转换（SQL Server 2017+支持）：

SELECT *
FROM Table_1 AS t1
LEFT JOIN (
    SELECT 
        *,
        TRY_CAST(
            (SELECT STRING_AGG(c, '') 
             FROM (SELECT SUBSTRING(tid, n, 1) AS c 
                   FROM (SELECT TOP (LEN(tid)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) AS nums 
                   WHERE SUBSTRING(tid, n, 1) LIKE '[0-9]') AS digits) AS INT
        ) AS numeric_tid
    FROM Table_2
) AS t2
ON t1.Termid = t2.numeric_tid

注意事项 #

• TRY_CAST仅支持SQL Server 2012及以上版本；若用更早版本，可结合CASE + ISNUMERIC，但ISNUMERIC对类似123.0这类字符串会误判为可转换，需额外处理。
• 若tid中数字部分超过INT范围，需改用BIGINT类型转换。

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