SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL Server 2005 does not have that either. I have created very simple UDF which does the same work.

FOR SQL SERVER 2000:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

FOR SQL SERVER 2005:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

Both the above UDF can be tested with following script
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'

Here is the quick video on the same subject:

There will be no spaces around them. It is very little but useful trick.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Six Properties of Relational Tables
Next Post
SQL SERVER – 2005 Take Off Line or Detach Database

Related Posts

127 Comments. Leave new

Leave a Reply

Menu