SQL Server中批量统计全表各列指定字符出现次数的方法

嘿，这个需求我太熟了！要在SQL Server里遍历临时表#tvshows的所有列，统计每列中|的出现次数，还得适配任意列数，完全不用给每列写重复代码，用动态SQL就搞定了。下面给你两种方案，分别适配不同版本的SQL Server：

方案一：SQL Server 2017及以上版本（支持STRING_AGG） #

这个版本用STRING_AGG拼接列的统计语句非常方便，代码如下：

DECLARE @DynamicSQL NVARCHAR(MAX);

-- 构建动态SQL语句，遍历所有列生成统计逻辑
SELECT @DynamicSQL = STRING_AGG(
    CONCAT(
        'SELECT ''', name, ''' AS ColumnName, ',
        'SUM(ISNULL(LEN(', QUOTENAME(name), ') - LEN(REPLACE(', QUOTENAME(name), ', ''|'', '''')), 0)) AS VerticalBarCount ',
        'FROM #tvshows'
    ),
    ' UNION ALL '
)
FROM sys.columns
WHERE object_id = OBJECT_ID('tempdb..#tvshows'); -- 定位临时表#tvshows

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

代码解释： #

• sys.columns：系统视图，用来获取#tvshows的所有列名，通过OBJECT_ID('tempdb..#tvshows')定位临时表（临时表默认存在tempdb库中）。
• STRING_AGG：把每列对应的统计SQL用UNION ALL拼接起来，形成完整的查询语句。
• LEN(col) - LEN(REPLACE(col, '|', ''))：核心逻辑，计算单个单元格中|的数量——替换掉所有|后，原长度和新长度的差值就是该单元格里|的个数。
• ISNULL(..., 0)：处理NULL值，因为NULL列的LEN结果是NULL，转换成0避免统计错误。
• SUM(...)：对整列的所有行求和，得到该列中|的总出现次数。

方案二：SQL Server 2016及更早版本（不支持STRING_AGG） #

如果你的SQL Server版本比较老，用FOR XML PATH来拼接字符串：

DECLARE @DynamicSQL NVARCHAR(MAX);

-- 构建动态SQL语句
SELECT @DynamicSQL = STUFF(
    (
        SELECT ' UNION ALL ' +
            CONCAT(
                'SELECT ''', name, ''' AS ColumnName, ',
                'SUM(ISNULL(LEN(', QUOTENAME(name), ') - LEN(REPLACE(', QUOTENAME(name), ', ''|'', '''')), 0)) AS VerticalBarCount ',
                'FROM #tvshows'
            )
        FROM sys.columns
        WHERE object_id = OBJECT_ID('tempdb..#tvshows')
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'),
    1, 11, '' -- 去掉开头多余的' UNION ALL '
);

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

使用注意事项： #

• 不管是3列还是150列，这个方案都能自动适配，不需要修改代码。
• 如果你的列名包含特殊字符（比如空格、中文），QUOTENAME(name)会自动给列名加上方括号，避免语法错误。
• 临时表#tvshows必须在当前会话中存在，否则sys.columns找不到对应的列。

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