MSSQL Right / Left Trim by Character

Posted on

Question :

How can I trim last (or first) characters from a string in SQL server?
Given a string like '~~abc~~', the output will be '~~abc'.

For example, in Postgresql, SELECT trim(trailing '~' FROM '~~abc~~') returns '~~abc'.

Answer :

There should be more efficient methods if you are doing this for a lot of data, but one way to do this could be by using LEFT(), RIGHT() and PATINDEX()

DECLARE @string varchar(255) = '~~abc~~';

SELECT RIGHT(@string,LEN(@String)-PATINDEX('%[^~]%',@string)+1); -- Remove leading ~
SELECT LEFT(@string,LEN(@String)-PATINDEX('%[^~]%',REVERSE(@string))+1);-- Remove trailing ~

Results

(No column name)
abc~~

(No column name)
~~abc

Leave a Reply

Your email address will not be published.