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
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
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
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 ???….