SQL Server T-SQL TRIM END

I need to trim ;开发者_开发问答 from a string inside T-SQL. But only if it has it. Like below :

DECLARE @_tags AS NVARCHAR(MAX);
SET @_tags = 'bla; bla;';

--SELECT TRIMEND(@_tags, ';');

So, if @_tags ends with ;, ; will be trimmed. How can I do that?

---

To remove all if last char is ;:

SELECT 
   CASE
       WHEN RIGHT(@_tags, 1) = ';' THEN REPLACE (@_tags, ';', '')
       ELSE @_tags
   END

To remove the end one only:

SELECT 
   CASE
       WHEN RIGHT(@_tags, 1) = ';' THEN STUFF(@_tags, LEN(@_tags), 1, '')
       ELSE @_tags
   END

---

Gbn's solution is better than this one if there's at most one ; at the end of a string.

But if you'd like to strip an arbitrary number of ; consider this query:

select  case 
        when patindex('%[^;]%', strCol) = 0 then ''
        else substring(strCol, 1, 
            len(strCol) - patindex('%[^;]%', reverse(strCol)) + 1)
        end
from    YourTable

The pattern %[^;]% matches any character that's not ;. If you combine it with reverse, you can search for the last instead of the first match. Example at SE Data.

---

I'd like to generalize the answer of gbn. This UDF will trim a variable string @TO_TRIM from the end of @STRING

CREATE FUNCTION [dbo].[TRIMEND](@STRING  NVARCHAR(MAX), @TO_TRIM NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS
BEGIN
  RETURN 
     CASE
        WHEN @STRING IS NULL THEN NULL
        WHEN RIGHT(@STRING, LEN(@TO_TRIM)) = @TO_TRIM THEN STUFF(@STRING, LEN(@STRING) - LEN(@TO_TRIM) + 1, LEN(@TO_TRIM), N'')
        ELSE @STRING
     END
END

So you can

SELECT [dbo].[TRIMEND](@_tags, ';');