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
Hi Mr.Dave,
I am working on a query where i have to seperate the records from a column and the column is like this-
Category
Request/Phone/Apply
Break/Hp/Desktop
Can you tell me how can i seperate the records like
Category1 Category2 Category3
Request Phone Apply
Break Hp Desktop
Thanks and Regards
Nick
Hi Nick,
You can use XML to partition this string into columns as:
DECLARE @xmlIDs XML
DECLARE @Tmp TABLE(
ID INT IDENTITY,
Val VARCHAR(MAX)
)
INSERT INTO @Tmp(Val)
SELECT ‘Request/Phone/Apply’
UNION ALL
SELECT ‘Break/Hp/Desktop’
SELECT ID,
x.query(‘IDs/ID[1]’).value(‘.’,’VARCHAR(MAX)’) AS C1,
x.query(‘IDs/ID[2]’).value(‘.’,’VARCHAR(MAX)’) AS C2,
x.query(‘IDs/ID[3]’).value(‘.’,’VARCHAR(MAX)’) AS C3
FROM (
SELECT *,
CAST(‘
‘ + REPLACE(Val, ‘/’, ”) + ” +
” AS XML) AS X
FROM @Tmp
) x
For more reference you can go:
Thanks,
Tejas
Hey Tejas,
Thanks for your reply. let me show you what i have done.
I wanna develop report using the query below but when i run it in SSRS it says “Invalid object name #temp”
can you help me with this.
Thanks
Nick
——————————————–
declare @string varchar(500)
declare @i int
declare @i1 int
set @i1 =1
declare @incident int
declare @pos int
declare @piece varchar(500)
create table #temp(valmain int,val0 int, val1 varchar(50))
DECLARE db_cursor CURSOR FOR
select top 10 workitem_category_tree_value
,count(workitem_number) as Incidents
from hd_workitem_current_view
where workitem_created_on between ‘1/8/2010’and’1/15/2010’
group by workitem_category_tree_value
order by count(workitem_number)DESC
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @string,@incident
WHILE @@FETCH_STATUS = 0
BEGIN
if right(rtrim(@string),1) ‘\’
set @string = @string + ‘\’
set @pos = patindex(‘%\%’ , @string)
set @i =0
insert into #temp values(@i1,@i,@incident )
while @pos 0
begin
set @i=@i+1
set @piece = left(@string, @pos – 1)
print cast(@piece as varchar(500))
print cast(@pos as varchar(500))
insert into #temp values(@i1,@i,@piece)
— You have a piece of data, so insert it, print it, do whatever you want to with it.
–print cast(@piece as varchar(500))
set @string = stuff(@string, 1, @pos, ”)
set @pos = patindex(‘%\%’ , @string)
end
set @i1=@i1+1
FETCH NEXT FROM db_cursor INTO @string,@incident
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT valmain,
[0], [1], [2], [3]
FROM
(SELECT valmain,val0, val1
FROM #temp) AS SourceTable
PIVOT
(
min(val1)
FOR val0 IN ( [0], [1], [2], [3])
) AS PivotTable;
drop table #temp
——————————————————————
Hi dude,
Pls avoid using # table. while ur server get restarted or any network issue u will lose ur table.. use table and finally truncate it… this wont occupy much space too… ur coding correct anyhow..
thanks for the help
i hope you give the great response in future
Look at for a universal trim function.
LTRIM and RTRIM don’t remove double spaces if they lay between other characters
Hi,
Could u pls help me by providing solution to the below problem.
I have similar to below IIS requests, now I want to display only resource names from below requests,
for example, results should be like this…
Globalsign.js
simbol.jpg
Kindly help me. I am using SQL 2008.
Thanks,
Kishore
Hi dude ..
Check out
declare @hp varchar(200)
declare @hp1 varchar(200)
set @hp = ‘http://192.168.1.1/web/Styles/Globalsign.js’
set @hp1 = ‘http://192.168.1.1/web/forms/simbol.jpg’
select reverse(substring(reverse(@hp),1,charindex(char(47),reverse(@hp),1)-1))
select reverse(substring(reverse(@hp1),1,charindex(char(47),reverse(@hp1),1)-1))
is it correct pinal pls tell…
Here are the shortcuts
select RIGHT(@hp,charindex(‘/’,reverse(@hp))-1)
select RIGHT(@hp1,charindex(‘/’,reverse(@hp1))-1)
nice sir … actually this much only my mind works… gr8…
Hello Friends,
I have a small doubt, Plz help. “This World is Beautiful”
This is my String stored in DB but in output i only want
“This World”
Note: No double quote required.
Plz suggest
Regards
Anupam
Hi…
Try this…
select replace(‘“This World is Beautiful”’,’ is Beautiful’,’ ‘)
Hi,
I have to bind a nvarchar column to sql reporting services, and need to format it, if it has space between the 2 lines. Kindly help me how to do it?
Eventhough I used TRIM function its not working.LTRIM(RTRIM(‘LOt ‘)) gives me only ‘LOt ‘.Can anyone tell me what is wrong here?
how can i reduce middle space in value ?
ex:-
lap top
insert into table_1 (name) values (lap top);
i want value “laptop” stored in database
pls
send method name
insert into table_1 (name)
select replace(‘lap top’,’ ‘,”)
While the trim() is convenient, it, as with all UDFs in SQL Server, can be a performance hog. Run the following and you can see a dramatic difference. I ran on SQL Server 2008:
create function dbo.Trim
(
@string varchar(max)
)
returns varchar(max) as
begin
return( rtrim(ltrim(@string)) );
end;
go
declare
@i integer = 0,
@dummy varchar(10),
@start datetime;
set @start = current_timestamp;
while (@i < 10000) begin
set @dummy = dbo.trim( ' foo bar ');
set @i = @i + 1;
end;
print 'UDF: ' + cast(datediff( millisecond, @start, current_timestamp) as varchar);
set @i = 0;
set @start = current_timestamp;
while (@i < 10000) begin
set @dummy = rtrim(ltrim(' foo bar '));
set @i = @i + 1;
end;
print 'Built-ins: ' + cast(datediff( millisecond, @start, current_timestamp) as varchar);
My results consistently show the UDF taking about 30 times longer to perform.
Hello,
Can anyone tell me how to call any UDF without schema name?
I have created Trim function but while calling it my requirement is i should not use dbo.Trim instead i will be using Trim only. is there any way to do that?
I have tried creating sys.Trim also but it is throwing me error as
The specified schema name “sys” either does not exist or you do not have permission to use it.
Can anyone tell me how to do this?
I am using SQL SERVER 2005..
I am able to trim values using LTRIM with the following
SELECT LTRIM(‘ ZAM000000017’) it is giving proper value as ZAM000000017
But when I am trying to use Update Statement to update the value
Update INTERMEDIATE_ISIN
Set ISIN_CODE = LTRIM(ISIN_CODE);
is not working…. !!!
Please help
hi this is nani,
Can anyone tell me how to get email id value with out alias name like.. i hav id “naresh@softsolvers.co.my” i want only “naresh” for result..
please help..
thanq,
select left(email,charindex(‘@’,email)-1)
why not just do LTRIM(RTRIM(COLUMN_NAME)) ?
Hi Pinal,
I am having an issue while trimming the below word.
ETF factsheets request
Select RTrim(Len(Comments)), Comments from De_Activity Where [ID] = 1470
Select LTrim(Len(Comments)), LTrim(Comments) from De_Activity Where [ID] = 1470
Total length of string is 28 (Sql). But when I do the trim in excel I get len as 22.
I have used charindex to find ‘E’, output was 4.
So there are 3 spaces before E, but are not getting trimmed.
Regards,
Uday
Hi.
I am using MS SQL 2008. I have a table X in DB which has some y columns. One of the column values has spaces suffixed and LTRIM, RTRIM is not working. Please let me kow if there is any other way to get rid of spaces.
Hi..I’m facing a problem from containstable in sql server.I wrote a search procedure including containstable.the results came fine.but i’m facing problem with the search strings “all” and “h&a”.i didn’t got any result when using these strings.but in table keywords(All and H&A) is there can u please help to me for avoid the problem.
How can i remove middle characters from a string?
eg: phaninder,reddy is a name……..i would like to remove ‘,’ from this name using trim function..pls give me the query…..
You cannot use trim function to remove a comma. Use replace function
select replace(col,’,’,”) from table
I have to bind a nvarchar column to sql reporting services, and need to format it, if it has space between the 2 lines. Kindly help me how to do it?
select LTRIM(RTRIM(columnname)) as outputcolumn from tablename