User Defined Trim Function in SQL Server

SQL Server has RTRIM() function that truncates all trailing blanks:

SELECT RTRIM('Hello world ') as Result;

rtrim_function_result

There is also LTRIM() function that truncates all leading blanks:

SELECT LTRIM(' Hello world') as Result;

ltrim_function_result

There is no TRIM() function that truncates all trailing and all leading blanks.
You can do that by combining RTRIM() and LTRIM() function in one query:

SELECT LTRIM(RTRIM(' Hello world ')) AS Result;

ltrim_rtrim_function_result

Or you can create a User Defined Function (UDF) that truncates all trailing and all leading blanks:

CREATE FUNCTION dbo.ufnTRIM(@string NVARCHAR(4000))
RETURNS NVARCHAR(4000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

To run the new function:

SELECT dbo.ufnTRIM(' Hello world ') AS Result;

ufnTRIM_function_result

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">