SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video

Trim is one of the most frequently used operation over String data types. A developer often come across a scenario where they have the string with leading and trailing spaces around string. If your business logic suggests that the logs around the spaces are not useful they should be trimmed. However, in SQL Server there is no TRIM function. When a TRIM function is used it will throw an error.

For example, here is the script when executed it will throw an error.

-- The following will throw an error
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT TRIM(@String1) TrimmedValue
GO

The above script will return following error:

Msg 195, Level 15, State 10, Line 4
'TRIM' is not a recognized built-in function name.

Let us not everything why this simple function is not implemented but try to resolve how we can achieve the result of the same function. SQL Server has two functions which when nested can give us the same result as a TRIM function.

1) RTRIM - Removes the Spaces on the right side (or leading spaces) of the string

2) LTRIM - Removes the Spaces on the left side (or trailing spaces) of the string

We can combine them as following and it will not throw an error.

-- The following will work
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO

Let us see the same concept in following SQL in Sixty Seconds Video:

Additionally, if you want you can create a user defined function which is using RTRIM and LTRIM and can use the function when trim functionality is required.

-- Create Function
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO
SELECT dbo.TRIM(' String ')
GO

Now when we are on the topic of the TRIM function, let me remind you one very important impact of this function if used in the WHERE clause. If any function is used in the WHERE clause, it will negatively impact on the performance of the query. SQL Server has to process the function on whole column leading Table Scan or Index Scan instead of Index Seek. This will increase the resource utilization and lead to poor performance. However, using this function in SELECT statement does not degrade performance much. In simple words - please be mindful of using any functions. Use the functions when you absolutely need it or enforcing business needs.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

About these ads

10 thoughts on “SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video

  1. Hi Pinal,

    Small Mistake in below Post

    2) RTRIM – Removes the Spaces on the left side (or trailing spaces) of the string.

    *Thanks & Regards,*

    Srinivas.K

    • How do you figure? RTrim removes the trailing spaces (i.e. thos which come AFTER the text), which are those on the Right. That’s why the “R” in RTrim. The spaces on the left are leadings spaces because they come first, thus the “L” in LTrim.

  2. question about Trim in comparison statements in where clause when data type is a fixed length char. At one time in SQL Server T-SQL at least it was not possible to get a correct match when look for “WHERE Field1 = ‘at’ ” when Field1 was a char column of length 5. It was necessary to do either “WHERE Field1 = ‘at ‘ ” or “WHERE RTrim(Field1) = ‘at’ “. Based on your recommendations against it are you saying that this is no longer necessary?

  3. I strongly believe one has use the “Replace” function too. This too will work.

    SELECT REPLACE(RTRIM(LTRIM(Column_Name)),’ ‘,”) FROM Table

    This query works fine in SQL Server 2008 and R2

    • You have to be careful of the data you are comparing. Including the REPLACE function will remove spaces internally in the column data. If you are looking for a result with a space in it, this won’t work. Example, utilizing REPLACE would take the value “JOHN SMITH” and return “JOHNSMITH”. I would recommend only using REPLACE where you are sure you will need it rather than with every LTRIM or RTRIM call.

  4. Thanks for the tip! I thought for sure that TRIM was a default function of MS SQL but leave it to M$ for being weird. Thanks for clearing this up man!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s