如何在SQL查询结果中去除带括号的颜色代码（如<#0077d3>）

针对你的需求，需要移除查询结果中类似<#0077d3>的颜色代码，以下是两种可行方案：

方案一：创建自定义函数（推荐，可复用） #

先创建一个用于移除所有<#xxxxxx>格式颜色代码的函数：

CREATE FUNCTION dbo.RemoveColorCodes(@input VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @pattern VARCHAR(20) = '%<#______>%'
    -- 循环匹配并删除所有颜色代码，直到无匹配项
    WHILE PATINDEX(@pattern, @input) > 0
    BEGIN
        -- <#xxxxxx>共9个字符，从匹配位置开始删除9个字符
        SET @input = STUFF(@input, PATINDEX(@pattern, @input), 9, '')
    END
    RETURN @input
END
GO

然后修改原查询，将包含颜色代码的字段用该函数包裹：

SELECT 
    ROW_NUMBER() OVER (ORDER BY c.[exp] DESC) as [rank],
    dbo.RemoveColorCodes(c.[name]) AS [name],
    c.[lv],
    c.[exp],
    dbo.RemoveColorCodes(ISNULL(b.[name],'n/a')) AS [guild],
    ISNULL(b.[icon],'none.jpg') AS [guild_icon],
    dbo.RemoveColorCodes(c.[job]) AS [job],
    CAST(
        CASE
            WHEN c.[login_time] > c.[logout_time] THEN 'ON'
            ELSE 'OFF'
        END AS VARCHAR
    ) AS [stat]
FROM [Telecaster].[dbo].[Character] c
LEFT JOIN [Telecaster].[dbo].[GuildMember] a ON c.[sid] = a.[player_id]
LEFT JOIN [Telecaster].[dbo].[Guild] b ON b.[sid] = a.[guild_id]
WHERE 
    c.[permission] < 100 
    AND c.[name] NOT LIKE '%@%' 
    AND c.[lv] < 300

方案二：临时查询内处理（无需创建函数） #

如果不想创建函数，可使用递归CTE处理，但代码较繁琐，仅适合临时场景：

WITH CleanedData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY c.[exp] DESC) as [rank],
        c.[name] AS raw_name,
        c.[lv],
        c.[exp],
        ISNULL(b.[name],'n/a') AS raw_guild,
        ISNULL(b.[icon],'none.jpg') AS [guild_icon],
        c.[job] AS raw_job,
        CAST(
            CASE
                WHEN c.[login_time] > c.[logout_time] THEN 'ON'
                ELSE 'OFF'
            END AS VARCHAR
        ) AS [stat]
    FROM [Telecaster].[dbo].[Character] c
    LEFT JOIN [Telecaster].[dbo].[GuildMember] a ON c.[sid] = a.[player_id]
    LEFT JOIN [Telecaster].[dbo].[Guild] b ON b.[sid] = a.[guild_id]
    WHERE 
        c.[permission] < 100 
        AND c.[name] NOT LIKE '%@%' 
        AND c.[lv] < 300
),
CleanedName AS (
    SELECT 
        [rank],
        raw_name,
        CASE WHEN PATINDEX('%<#______>%', raw_name) > 0 
             THEN STUFF(raw_name, PATINDEX('%<#______>%', raw_name), 9, '')
             ELSE raw_name END AS cleaned_name,
        [lv], [exp], raw_guild, [guild_icon], raw_job, [stat]
    FROM CleanedData
    UNION ALL
    SELECT 
        [rank],
        cleaned_name,
        CASE WHEN PATINDEX('%<#______>%', cleaned_name) > 0 
             THEN STUFF(cleaned_name, PATINDEX('%<#______>%', cleaned_name), 9, '')
             ELSE cleaned_name END AS cleaned_name,
        [lv], [exp], raw_guild, [guild_icon], raw_job, [stat]
    FROM CleanedName
    WHERE PATINDEX('%<#______>%', cleaned_name) > 0
),
CleanedGuild AS (
    SELECT 
        [rank], cleaned_name, [lv], [exp],
        CASE WHEN PATINDEX('%<#______>%', raw_guild) > 0 
             THEN STUFF(raw_guild, PATINDEX('%<#______>%', raw_guild), 9, '')
             ELSE raw_guild END AS cleaned_guild,
        [guild_icon], raw_job, [stat]
    FROM CleanedName
    WHERE PATINDEX('%<#______>%', cleaned_name) = 0
    UNION ALL
    SELECT 
        [rank], cleaned_name, [lv], [exp],
        CASE WHEN PATINDEX('%<#______>%', cleaned_guild) > 0 
             THEN STUFF(cleaned_guild, PATINDEX('%<#______>%', cleaned_guild), 9, '')
             ELSE cleaned_guild END AS cleaned_guild,
        [guild_icon], raw_job, [stat]
    FROM CleanedGuild
    WHERE PATINDEX('%<#______>%', cleaned_guild) > 0
),
CleanedJob AS (
    SELECT 
        [rank], cleaned_name, [lv], [exp], cleaned_guild,
        [guild_icon],
        CASE WHEN PATINDEX('%<#______>%', raw_job) > 0 
             THEN STUFF(raw_job, PATINDEX('%<#______>%', raw_job), 9, '')
             ELSE raw_job END AS cleaned_job,
        [stat]
    FROM CleanedGuild
    WHERE PATINDEX('%<#______>%', cleaned_guild) = 0
    UNION ALL
    SELECT 
        [rank], cleaned_name, [lv], [exp], cleaned_guild,
        [guild_icon],
        CASE WHEN PATINDEX('%<#______>%', cleaned_job) > 0 
             THEN STUFF(cleaned_job, PATINDEX('%<#______>%', cleaned_job), 9, '')
             ELSE cleaned_job END AS cleaned_job,
        [stat]
    FROM CleanedJob
    WHERE PATINDEX('%<#______>%', cleaned_job) > 0
)
SELECT 
    [rank],
    cleaned_name AS [name],
    [lv], [exp],
    cleaned_guild AS [guild],
    [guild_icon],
    cleaned_job AS [job],
    [stat]
FROM CleanedJob
WHERE PATINDEX('%<#______>%', cleaned_job) = 0

说明 #

• 方案一的自定义函数可重复使用，代码简洁易维护，适合需要多次处理颜色代码的场景。
• 函数通过PATINDEX匹配颜色代码格式，WHILE循环确保所有匹配项都被移除。
• 原查询中补充了表别名c.[sid]，避免关联条件的字段歧义。

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