I have a query that returns results but I want to delete the data inside the parenthesis like <#0-CodePudding

User code I used the code to display the results

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

Actual results

desired results

I want to delete the color code along with the brackets

CodePudding user response：

 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

I assumed the color String format would be similar across the records.

CodePudding user response：

I would create a scalar function to remove the tags. Here's fiddle on how to do it. You could just do a STUFF in your original query but there's no error checking like you can do in a function.

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);

(No column name)
ASTA

fiddle

Here's how I would do it inline using STUFF. I cleaned up your code a bit:

SELECT 
  ROW_NUMBER() OVER (ORDER BY c.[exp] DESC) as [rank]
  , CASE 
      WHEN (c.[name] LIKE '<#%' AND c.[name] LIKE '%>%') 
        THEN STUFF(c.[name],1,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]
  , CASE
      WHEN c.[login_time] > c.[logout_time]
        THEN 'ON'
      ELSE 'OFF'
    END 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