SQL SERVER – 2008 – TRIM() Function – User Defined Function

“How come SQL Server 2000, 2005 does not have function TRIM()? Is there any way to get similar results.

What about SQL Server 2008?”

James has asked very interesting question. I have previously wrote about SQL SERVER – TRIM() Function – UDF TRIM(). Today my answer is no different than what I answered in earlier post.

SQL Server does not have function which can trim leading or trailing spaces of any string at the same time. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. SQL Server 2008 also does not have TRIM() function. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.

SELECT RTRIM(LTRIM(' Word ')) AS Answer;

Should give result set without any leading or trailing spaces.

Answer
——
Word

I have created following UDF which everyday when I have to TRIM() any word or column.
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))RETURNS VARCHAR(MAX)BEGIN
RETURN LTRIM(RTRIM(@string))ENDGO
Now let us test above UDF running following statement where there are leading and trailing spaces around word.
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'
There will be no spaces around them. If extra spaces are useless data, when data is inserted in database they should be trimmed. If there is need of spaces in data but in certain cases they should be trimmed when retrieving we can use Computed Columns. Read more about computed columns SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation.

Following example demonstrates how computed columns can be used to retrieve trimmed data.

USE AdventureWorks
GO/* Create Table */CREATE TABLE MyTable(ID TINYINT NOT NULL IDENTITY (1, 1),FirstCol VARCHAR(150) NOT NULL,TrimmedCol AS LTRIM(RTRIM(FirstCol))
) ON [PRIMARY]
GO/* Populated Table */INSERT INTO MyTable([FirstCol])SELECT ' Leading'UNION
SELECT 'Trailing 'UNION
SELECT ' Leading and Trailing 'UNION
SELECT 'NoSpaceAround'GO/* SELECT Table Data */SELECT *FROM MyTable
GO/* Dropping Table */DROP TABLE MyTable
GO

Above query demonstrates that when retrieving data it retrieves trimmed data in column TrimmedCol. You can see the result set in following image.

Computed columns are created run time and performance may not be optimal if lots of data is being retrieved. We will see some other time how we can improve the performance of Computed Column using Index.

Resources:-

http://blog.sqlauthority.com/