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
is there any function in SQl Server which will return Spaces??
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…
It is becuase when you use Varchar datatype, by default spaces on the right are omitted. For Char datatype it means length is fixed and no need to trim it until needed.
What if the use wants to trim only at the one end?
Thats why you have seperate functions
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?
Try:
SELECT REPLACE(‘ All m y spaces will be re moved ‘,’ ‘,”)
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?
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.
it probably ‘doesnt work’ in your case because CHAR is a fixed length field, so if you trim:
‘Sample ‘
it becomes:
‘Sample’
but when you save it to a char(8) field, it becomes:
‘Sample ‘
because it is padded with spaces again.
well i tried to type 2 spaces after the word ‘sample’ but it looks like it got trimmed to just 1.
So… when you save
‘Samples’ to a char(8) field it becomes ‘Samples ‘
Yes that is how it works.
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)
https://docs.microsoft.com/en-us/sql/integration-services/expressions/trim-ssis-expression?view=sql-server-2017
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…