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:
[youtube=http://www.youtube.com/watch?v=1-hhApy6MHM]There will be no spaces around them. It is very little but useful trick.
Reference : Pinal Dave (https://blog.sqlauthority.com)
127 Comments. Leave new
Thanks for the tip, I didn’t realize the Trim function didn’t work on SQL Server 2005 either. I used this to do the trick thanks to your tip:
Select ltrim(rtrim(FIELD)) from TABLE
How do I do away with the extra characters in the field, sy that the third field will be displayed without the spaces.
—
—
if exists (select top 1 1 from information_schema.routines where
specific_name = ‘proc_DisplayHoursWorked’ and routine_type = ‘PROCEDURE’)
DROP PROCEDURE proc_DisplayHoursWorked
go
CREATE PROCEDURE [dbo].[proc_DisplayHoursWorked]
AS
SELECT Employees.Name,
Employees.Surname,
Employees.Name + ‘ ‘ + Employees.Surname As [EmpName]
FROM Timesheets INNER JOIN Employees
ON Timesheets.UserNumber = Employees.UserNumber
FOUND TO SOLUTION:
—
—
if exists (select top 1 1 from information_schema.routines where
specific_name = ‘proc_DisplayHoursWorked’ and routine_type = ‘PROCEDURE’)
DROP PROCEDURE proc_DisplayHoursWorked
go
CREATE PROCEDURE [dbo].[proc_DisplayHoursWorked]
AS
SELECT rtrim(Employees.Name) + ‘ ‘ +
ltrim(Employees.Surname) As [Emp Name]
FROM Timesheets INNER JOIN Employees
ON Timesheets.UserNumber = Employees.UserNumber
Hi Raghuram,
you can use this STUFF function to remove those characters
select stuff(stuff(‘(’13,14,15′)’,2,1,”),10,1,”)
Hi Raghuram,
you can use this STUFF function to remove those characters
select stuff(stuff(’(’13,14,15′)’,2,1,”),10,1,”)
how to remove first & last space in fields without use of replace function just like trim in .net…?
plz. reply me..
Hi,
You can use:
UPDATE Table
SET Column = LTRIM(RTRIM(Column))
It will update all your rows and remove space before and after.
Let me know if it helps you.
Thanks,
Tejas
HI Pinal,
Is there has any performance gain apart from using “ltrim (rtrim(col))” to UDF. Normally UDF bit less performance comparing system function.
Thank you,
Tharindu
@Erik,
Can your application call a SQL Server SP? If so, you could create an SP that calls the UDF.
Pinal,
I am trying to find a way to seperate a column into multiple columns, using a space as a delimeter. This would be similar to Excel’s ‘Text to Columns’ function. I have a field that has information like ‘FirstName LastName – Some Other Info’. What I need is just the first two bits of information as two seperate columns, so I can do some lookup and string functions against that data.
CREATE FUNCTION employees_in_project
(@pr_number varchar(20))
RETURNS TABLE
AS
RETURN (SELECT Sum(Amount) as Amount
FROM MasterDetail
WHERE MasterDetail.code = @pr_number)
GO
In Analizer
SELECT *, employees_in_project(master.code ) as Amount FROM Master
GO
it not work what is error in query
Hi pinaldave ,
Just wanted to say that you’ve got a great blog! In my various searches for clear instruction on how to do the various things that I find myself needing to do in SQL Server, I’ve found that it’s very close to a sure bet that if I find it on your blog, it’s going to be clear, and it’s going to work. Thanks!!!
Trim leading/trailing whitespace and carriage returns, whilst retaining chars in the entity body.
DECLARE @value VARCHAR(1000)
DECLARE @pat varchar(10)
SET @pat = ‘%[^ ‘ + char(09) + char(10) + ‘]%’
SET @value = CHAR(10) + ‘ ‘ + CHAR(10) + ‘message test ‘ + char(10) + ‘ values ‘ + CHAR(10) + ‘ ‘ + CHAR(10) + CHAR(10) + ‘ ‘ + CHAR(10)
SELECT SUBSTRING(
@value,
PATINDEX(@pat, @value),
LEN(@value) – PATINDEX(@pat, @value) – PATINDEX(@pat, REVERSE(@value)) + 2)
Hi caractacus,
Thank you for posting the more efficient method of trimming leading and trailing white space. I have a couple enhancements I would make, which i had to make on my side during testing.
1) Use DATALENGTH() instead of LEN(), because by design, LEN() omits trailing spaces, but when used in the SUBSTRING() function with the subtraction of the 2 PATINDEX() values, it ends up removing too many characters, in the case where the original string had trailing spaces.
2) I found that if the original string has ALL white-space characters (i.e. no non-whitespace characters), it returns the original string untouched, because the length minus 0 minus 0 plus 2 = length + 2. If you check for PATINDEX(@pat, @value) being 0, and simply returning ” (empty string), that should handle that case.
Other than that, this is a very slick way of identifying the first and last non-whitespace characters in a string and using those to remove the whitespace characters leading up to them from both ends.
This is a neat function and very often used when dealing with strings.
SQL Server could in fact include this feature in the database core. Of course one could always create this UDF but it would be more user friendly if it was already available as part of the system functions.
TRIM function is not implemented in SQL Server because doing a ltrim(rtrim()) can have serious performance penalties when dealing with large resultsets. If it is there by default, developers would tend to abuse its use.
By the way, great stuffs you have in here Pinal. Thanks!
LOLLLL it’s not there because it might be mis-used… good one.
UNIONS and JOINS can be expensive… better remove them as well….
guess it’s more efficent to force a UDF reference to do the identical thing….
Is there a way to select all columns (Select * from table) applying ltrim function on it. Meaning I don’t want to specify each column name in my query but I need the data returned without spaces.
Thanks.
@Ali.
You want to do this in another function or a stored procedure or simple a select statement ? Of-course this could be in stored procedure. Please give us more information.
~ IM.
Hi,
Please suggest a way to remove more than 2 spaces from the middle of string. As already suggested above, I’ve tried using replace () function but not getting the desired output.
ie.
DROP FUNCTION dbo.Trim
GO
CREATE FUNCTION dbo.Trim (@string varchar(8000))
RETURNS varchar (8000)
BEGIN
RETURN LTRIM (RTRIM (REPLACE (@string, ‘ ‘, ‘ ‘)))
END
GO
Am calling this function as –
SELECT dbo.Trim (‘ Hello Everybody ‘)
And the output is – ‘Hello Everybody’ (Still contains 2 spaces in middle).
How can I get just a single space in the middle ?
Hello Mani,
I think you are replacing 2 spaces with 1. For that the folowing code working perfrctly for me:
CREATE FUNCTION dbo.Trim (@string varchar(8000))
RETURNS varchar (8000)
BEGIN
RETURN LTRIM (RTRIM (REPLACE (@string, ‘ ‘, ‘ ‘)))
END
GO
Please let me know if this doesn’t asnwer your question.
Regards,
Pinal Dave
Hi Pinal,
Thanks for responding to my query. But my query is to remove all the spaces from the string except the single one. Just like the TRIM () function in MS Excel and the other programming languages. Pls. suggest how can I implement it ?
Regards.
Hi dude..
For that use replace command….
that means replace space to no space…
eg:select replace(‘ Hari priya ‘,’ ‘,”)