我有一个返回结果的查询，但我想删除括号内的数据，例如<#0077d3>

Question

用户代码我使用代码来显示结果

SELECT ( ROW_NUMBER() OVER (ORDER BY c.[exp] DESC)) as [rank], c.[name],c.[lv],c.[exp],
`ISNULL(b.[name],'n/a') [guild],
ISNULL(b.[icon],'none.jpg') [guild_icon],
c.[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 [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

实际结果

想要的结果

我想删除颜色代码以及括号

Answer 1

SELECT ( ROW_NUMBER() OVER (ORDER BY c.[exp] DESC)) as [rank], CASE WHEN (c.[name] LIKE '%<%' AND c.[name] LIKE '%>%') THEN  RIGHT(c.[name],LEN(c.[name])-CHARINDEX('>',c.[name])) ELSE c.[name] END [name],c.[lv],c.[exp],
ISNULL(b.[name],'n/a') [guild],
ISNULL(b.[icon],'none.jpg') [guild_icon],
c.[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 [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

我假定所有记录的颜色字符串格式是相似的。

Answer 2

我将创建一个标量函数来删除标记。下面是如何做到这一点的小提琴。你可以在你的原始查询中做一个STUFF，但没有错误检查，就像你可以在函数中做的。

CREATE FUNCTION fn_StripColorCode (@InputString nvarchar(512))
RETURNS nvarchar(512)
AS
BEGIN
DECLARE @first int = -1;
DECLARE @last int = -1;
--Find the start and end tags.
SET @first = CHARINDEX('<#', @InputString, 1);
SET @last = CHARINDEX('>', @InputString, 1);
--If either start or end is not found, return original string.
IF (@first < 0 OR @last < 0)
RETURN @InputString;
--If the start tag is found after the end tag, return the original string.
IF (@first > @last)
RETURN @InputString;
--Remove the tag.
DECLARE @result nvarchar(512);
SET @result = STUFF(@InputString, 1, @Last, '');
RETURN @result;
END
;

DECLARE @testName nvarchar(50) = '<#ff00c0>ASTA';
SELECT dbo.fn_StripColorCode(@testName);

(无列名)旅行社