There are several ways of doing this. For example you can use a small T-SQL block like this
BEGIN
DECLARE @data VARCHAR(100);
SET @data = ',,test1,test2 tag,test3 tag,,,';
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(@data, PATINDEX('%[^,]%', @data),99999))),99999));
END;
Or embed the logic into a select statement, for example
SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999)), PATINDEX('%[^,]%', REVERSE(SUBSTRING(MyColumn, PATINDEX('%[^,]%', MyColumn),99999))),99999))
FROM MyTable
However, the easy way would be to create a small function for the task, for example
TrimChar to remove desired character[
^]