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'
There will be no spaces around them. It is very little but useful trick.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
is there any function in SQl Server which will return Spaces??
Hi Susmita,
SQL Server has function called SELECT SPACE(numberofspace) to return spaces.
Regards,
Pinal Dave (SQLAuthority.com)
[...] 19th, 2007 by pinaldave A month ago, I wrote about SQL SERVER – TRIM() Function – UDF TRIM() . I was asked in comment if SQL Server has space [...]
Can I rtrim using a delimiter? I would like to trim the last characters following a “.”. Example 10.11.21.386 result to be 10.11.21
give the syntax of the trim function
INSERT INTO pack_items(items_id,items_code,items_price) Values (2,trim(’ HW6DH-COOP ‘),0.0850);
Why microsoft doesn’t provide a Trim() function that can trim spaces at both the ends…
Hi Stefen,
I wonder as well.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Is there any way to get this function in the sys schema?
CREATE FUNCTION sys.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
We’ve got a application that runs to an informix database
where all the users credentials are placed in.
When I run the application on the SQL Server 2005 database, it returns an error that says
‘TRIM’ is not a recognized built-in function name
I’m not able to change the application……
Can any one tell me how to trim hidden ascii chars?
Thanks
How can I trim spaces located in the middle of a string using SQL?
CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
CMIIW, this function cant return a really 8000 char does it ?
or even 257 char ? right ?
because the sql2k will truncate the result varchar into 256 char
anyone knows about solving this? or it’s just my weird sql 2k problem?
hi,
this is my first request to u.
would u mind to let me know,how can i use RTrim(fieldname) is not eual to space?
@erik,
I would prefer this function to be added in sys schema.
@Saru,
Any example of your ascii chars?
@Cristian,
You will have to use replace function?
@Peter Parker,
SQL Server 2005 returns varchar(max) sucessfully.
@ree,
not sure what you are asking, If you can explain your problem little more.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
any ideas on how to trim a text field? i.e. not varchar. ltrim and rtrim don’t work on fields of type text, apparently.
thanks.
L.
I hope MS SQL 2008 will have a TRIM function :(
I have one problem….
In the query i am getting the values like(’13,14,15′) so in that i am searching the data related to 13,14,15 so i have to trim the ‘ ‘ and finally i want the values like (13,14,15) so for this can u give any idea how to remove those ‘ ‘
Hi
I am attempting to migrate code from Oracle to SqlServer 2005
Why does select rtrim(ltrim(’ ‘)) not return null in Sqlserver ? Oracle does retrun a null
I also tested via your trim script
select isnull(dbo.trim(’ ‘),’Y') – returns ” not ‘Y’
What am I missing ?
Thanks
Hi All
I have looked every where to find my answer. May bee someone can help me here :)
I have an entry I need to trim. I looks like this:
“0114503401″0114503401<sip:014503401@14.208.19.114/5060_ID_ASDEWSDERFDE
The length of characters before <sip: are not the same.
The length of characters after @ are not the same.
I need to trim everything after the @ and everything before the <sip:.
My result needs to be 0114503401.
How now?
Thanx in advance.
:)
Vernon.
I would advise to use:
PATINDEX
and then
SUBSTRING.
Use books online to see the syntax.
PinalDave,
Interestingly enough SSIS has a TRIM.
SQL Server 2005 Books Online (September 2007)
TRIM (SSIS)
http://msdn2.microsoft.com/en-us/library/ms139947.aspx
Returns a character expression after removing leading and trailing spaces.
It does not seem that TRIM() will be included in SQL Server 2008.
Please, explain me about ltrim and rtim functions. I am tottaly lost.
thank you pinal
Hi ,
I tried adding the Trim function in sys schema.
CREATE FUNCTION sys.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
i get the following error:
Msg 2760, Level 16, State 1, Procedure TRIM, Line 4
The specified schema name “sys” either does not exist or you do not have permission to use it.
i am using a database where my id is the dbo.owner. i belive this should not be an authentication issue..
hello priti…
answer for ur trim function….
select rtrim(ltrim(motel_id)) from motel
ok this is work… this function trim all the spaces of right and left side….
and plz give me reply on friendship_ak1010@yahoo.co.in…
ok priti…
Sorry guys, but I find that trim function a little lame. All it does is save a handful of keystrokes. Here’s mine – it removes all whitespace, including tabs, carriage returns and line feeds. Further, it can be easily modified to trim any set of characters – just make @charstotrim a parameter.
ALTER function dbo.trim(@input varchar(8000)) returns varchar(8000) as
begin
declare @charstotrim varchar(100)
select @charstotrim =’ ‘+char(9)+char(10)+char(13)
while charindex(left(reverse(@input),1),@charstotrim) >0
select @input=left(@input,datalength(@input)-1)
while charindex(left(@input,1),@charstotrim) >0
select @input=substring(@input,2,datalength(@input)-1)
return @input
end
hi,
this is my first request to u.
I want to retrieve n capital characters from a field in sqlserver 2005.
Eg: ‘Lake Systems’-is the value in field
Desired Output-’LS’
Does anyone know if I can trim an entire field?
thanks
I’ve created my own trim and concat functions but I can’t call them with the dbo. because the same software works with DB2 and Oracle.
Any ideas…
Thanks
if i delete the record of 5th in product table,but not change the productid value,Productid value maintain 1,2,3,4,6..but i waunt 1,2,3,4,5… change of productid,Productid is the itentity
very helpful, thank you very much ;)
Hi,
I have an issue where I am not able to enter the data into the database during update statement.
The string datatype is nchar varying(127) so it seems to extend more than this and so its not updating the table.
I need a Trim function which will give me the first 126 chars of the string and then remove everything else, so that it will be updated in the table.
Thanks,
Lavanya.
I am trying to remove leading and trailing spaces in all columns in the database. Following is the query I am using, but an error Msg 1087, Level 15, State 2, Line 38
Must declare the table variable “@Tables”. Although the variable is declared.
The Query:
declare @Total_Columns int
declare @Columns Varchar(256)
Set @Columns = Null
Declare Sam_Cursor_Clm Cursor for
Select c.name from sys.columns c
inner join sys.objects t on c.object_id = t.object_id
where t.type = ‘u’
and t.name ’sysdiagrams’
Open Sam_Cursor_Clm
Fetch Next from Sam_Cursor_Clm
Into @Columns
declare @Tables Varchar(256)
Set @Tables = Null
Declare Sam_Cursor_Tbl Cursor for
Select t.name from sys.columns c
inner join sys.objects t on c.object_id = t.object_id
where t.type = ‘u’
and t.name ’sysdiagrams’
Open Sam_Cursor_Tbl
Fetch Next from Sam_Cursor_Tbl
Into @Tables
Set @Total_Columns = 0
while @Total_Columns < (Select count(c.name) from sys.columns c
inner join sys.objects t on c.object_id = t.object_id
where t.type = ‘u’
and t.name ’sysdiagrams’
)
Begin
Set @Total_Columns = @Total_Columns + 1
UPDATE @Tables
SET @Columns = LTRIM(RTRIM(@Columns))
Fetch Next from Sam_Cursor_Tbl
Into @Tables
Fetch Next from Sam_Cursor_Clm
Into @Columns
End
Close Sam_Cursor_Tbl
Deallocate Sam_Cursor_Tbl
Close Sam_Cursor_Clm
Deallocate Sam_Cursor_Clm
Please Help!
For some reason not equal sign did not print on screen
the code is:
and t.name ’sysdiagrams’
Same thing again:
the code is:
and t.name IS NOT EQUAL TO ’sysdiagrams’
Sorry Russ, but you can do better than that:
create function dbo.LTrimX(@str varchar(8000)) returns varchar(8000)
as
begin
declare @trimchars varchar(10)
set @trimchars = char(9)+char(10)+char(13)+char(32)
if @str like ‘[' + @trimchars + ']%’ set @str = substring(@str, Patindex(’%[^' + @trimchars + ']%’, @str), 8000)
return @str
end
go
create function dbo.RTrimX(@str varchar(8000)) returns varchar(8000)
as
begin
declare @trimchars varchar(10)
set @trimchars = char(9)+char(10)+char(13)+char(32)
if @str like ‘%[' + @trimchars + ']‘
set @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
return @str
end
go
create function dbo.TrimX(@str varchar(8000)) returns varchar(8000)
as
begin
return dbo.LTrimX(dbo.RTrimX(@str))
end
go
Sorry Russ, but I prefer the following implementation:
create function dbo.LTrimX(@str varchar(8000)) returns varchar(8000)
as
begin
declare @trimchars varchar(10)
set @trimchars = char(9)+char(10)+char(13)+char(32)
if @str like ‘[' + @trimchars + ']%’ set @str = substring(@str, Patindex(’%[^' + @trimchars + ']%’, @str), 8000)
return @str
end
go
create function dbo.RTrimX(@str varchar(8000)) returns varchar(8000)
as
begin
declare @trimchars varchar(10)
set @trimchars = char(9)+char(10)+char(13)+char(32)
if @str like ‘%[' + @trimchars + ']‘
set @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
return @str
end
go
create function dbo.TrimX(@str varchar(8000)) returns varchar(8000)
as
begin
return dbo.LTrimX(dbo.RTrimX(@str))
end
go
[...] 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 [...]
Is there any way to run this function on an entire column, to update/remove leading spaces?
Can i trim ” ” double quotation
sample: “Gerald Famisaran”
desired Output: Gerald Famisaran
pls….
I have a CSV file in which each record gives the name of PDF files which look something like this:
“FS-EST-008_0678951-01_janedoe_ab93fd78.pdf”
I have created a DTS to make the _ the delimiter and the text qualifier is set to “none”. The result is 4 columns in my SQL table. I am only using the first two columns and have added a 3rd column to include the [Date of Import]. The table is appended daily and I am executing a SQL Task as follows:
Update [DCSSFTPData].[dbo].[PD-Q058-ALT-LBP]
Set [Date of Import] = cast(getdate() as varchar(12))
where [date of import] is null
Then executing a separate SQL Task as follows:
delete from [DCSSFTPData].[dbo].[PD-Q058-ALT-LBP]
where DATEDIFF(day, [date of import], GETDATE()) > 90
All is well except……How do I get rid of those pesky double quotes at the beginning and end of each record? I’ve tried MID, REPLACE, TRIM, and I must not be using the right syntax for any of them.
I’ve only been working with SQL for 4 months, with no training, so I’m flying by the seat of my pants and could sure use some expert assistance! THANK YOU SOOOO MUCH!
Laurie
Laurie -
You are mixing and matching. You cannot use _ as a delimiter and also have it recognize the double quotes as text qualifier. Either one or the other, as you can’t break up a column in the middle of your text qualifiers.
What I’d do… is pull in the data in its existing format and manipulate it as needed afterwards.
1. Use the double quotes as text qualifier, and pull the string into one column on your import, say into column called import_str.
2. Then add two columns to the table created in the import process, and rip apart your string as needed.
Something like this…
ALTER TABLE importTable add rip_out_str1 varchar(255)
ALTER TABLE importTable add rip_out_str2 varchar(255)
– extract first col and remove extracted str from raw data
UPDATE importTable
SET rip_out_str1 = SUBSTRING(import_str,0,CHARINDEX(’_', import_str)),
import_str = replace(import_str, rip_out_str1+’_', ”)
– extract second col and remove extracted str from raw data
UPDATE importTable
SET rip_out_str2 = SUBSTRING(import_str,0,CHARINDEX(’_', import_str)),
import_str = replace(import_str, rip_out_str2+’_', ”)
… etc as needed
2005DBA
[...] SQL SERVER – TRIM() Function – UDF TRIM() [...]
[...] found following script posted by Russ and Erik. It is modified a bit from original [...]
I’m writing a batch job using .NET and want to compare a table in our data warehouse with a transactional table, These 2 tables reside on different servers and databases. I am creating a datahandler from .NET and have been told that creating 2 different data connections will not work ???
Any other ideas or will 2 different connections work with two seperate data adaptors? Any ideas welcome or if there is a better idea please share.
thanks
One thing is very clear. People like you make the world better place.
Good Work pinaldave!!!
—-URGENT—–
i have bulk inserted data in my sql 2005 server then i use trim (ltrim & rtrim) function still SPACE IS NOT REMOVED.
my imported table is all fileds are nvcahractor (255)
could you please advice what is happening here ???….
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
[...] SQL SERVER – TRIM() Function – UDF TRIM() [...]
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,”)