MSSQL Right / Left Trim by Character
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