/*
Author: Anjali Bansal
Date : DEC 14, 2016

select dbo.fn_RemoveLeadingTrailingChar(',1,2,3,4,5,,,',',')
select dbo.fn_RemoveLeadingTrailingChar('',',')
select dbo.fn_RemoveLeadingTrailingChar(null,',')

*/
CREATE FUNCTION fn_RemoveLeadingTrailingChar (
	@str NVARCHAR(MAX) = NULL
	,@charToRemove CHAR(1) = ','
	)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @lastChar CHAR(1)
		,@FirstChar CHAR(1)

	IF (
			@str IS NOT NULL
			AND @str <> ''
			)
	BEGIN --------  to remove trailing character
		SET @str = LTRIM(RTRIM(@str))
		SET @lastChar = SUBSTRING(@str, LEN(@STR), LEN(@STR) - 1) --SUBSTRING(column_name,start,length)

		WHILE (@lastChar = @charToRemove)
		BEGIN
			IF (@lastChar = @charToRemove)
			BEGIN
				SET @str = SUBSTRING(@STR, 0, LEN(@str))
			END

			SET @lastChar = SUBSTRING(@str, LEN(@STR), LEN(@STR) - 1)
		END

		--
		SET @FirstChar = SUBSTRING(@str, 1, 1) --SUBSTRING(column_name,start,length)

		WHILE (@FirstChar = @charToRemove)
		BEGIN
			IF (@FirstChar = @charToRemove)
			BEGIN
				SET @str = SUBSTRING(@STR, 2, LEN(@str))
			END

			SET @FirstChar = SUBSTRING(@str, 1, 1)
		END
	END

	RETURN @str
END