Trim Characters from Front and Last of the string.

I came across a very useful post. So wanted to share this everyone:

 https://raresql.com/2013/05/20/sql-server-trim-how-to-remove-leading-and-trailing-charactersspaces-from-string/

The Below solution is for trimming any character fro left and right

Left Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the LEFT side of the string.

CREATE FUNCTION dbo.L_TRIM(@String VARCHAR(MAX), @Char varchar(5)) RETURNS VARCHAR(MAX) BEGIN    RETURN SUBSTRING(@String,PATINDEX('%[^' + @Char + ' ]%'                     ,@String),DATALENGTH(@String)) END GO --In this example, the user defined function will remove spaces --and * from the left side of the string SELECT '  **  THIS IS A TEST STRING  ***  ' AS [String before Left trim] , dbo.L_TRIM('  **  THIS IS A TEST STRING  ***  ','*') AS [String After Left trim] GO --In this example, the user defined function will remove spaces --and 0 from the left side of the string SELECT ' 0001234' AS [String before Left trim] , dbo.L_TRIM(' 0001234','0')  [String After Left trim] GO --OUTPUT

Right Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the RIGHT side of the string.

CREATE FUNCTION dbo.R_TRIM(@String VARCHAR(MAX), @Char varchar(5)) RETURNS VARCHAR(MAX) BEGIN   RETURN REVERSE(SUBSTRING(REVERSE(@String)         ,PATINDEX('%[^' + @Char + ' ]%'         ,REVERSE(@String)),DATALENGTH(@String))) END GO SELECT '  **  THIS IS A TEST STRING  ***' AS [String before Right trim] , dbo.R_TRIM('  **  THIS IS A TEST STRING  ***  ','*') AS [String after Right trim] GO SELECT '12340000 ' AS [String before Right trim] , dbo.R_TRIM('12340000 ','0') AS [String after Right trim] GO --OUTPUT

Trim :
Given below is the solution that can help you to trim(remove) the spaces and characters from the BOTH sides of the string.

CREATE FUNCTION dbo.TRIM(@String VARCHAR(MAX), @Char varchar(5)) RETURNS VARCHAR(MAX) BEGIN   RETURN SUBSTRING(@String,PATINDEX('%[^' + @Char + ' ]%',@String)         ,(DATALENGTH(@String)+2 - (PATINDEX('%[^' + @Char + ' ]%'         ,REVERSE(@String)) + PATINDEX('%[^' + @Char + ' ]%',@String)         ))) END GO SELECT '  **  THIS IS A TEST STRING  ***  '  AS [String before trim] , dbo.TRIM('  **  THIS IS A TEST STRING  ***  ','*')  AS [String after trim] GO SELECT ' 000012340000 ' AS [String before trim] , dbo.TRIM(' 000012340000 ','0') AS [String after trim] GO