SQL SERVER – TRIM() Function – UDF TRIM()

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.

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

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 ‘ ‘

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.

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.

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

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.

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.

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.

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!!!

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.

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 ?

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

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))

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);

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.

I am suffering from a problem. there is one database field that is entered by users. User may some time entered lot of spaces between characters. This problems in crystal report. few line print on one page and other lines print on 2nd and 3rd page. I checked but this issue not belong to crystal report formatting. I search a lot for crystal report formulas to get solution but i can’t.
I want to remove empty lines between this string. how can i do it?

hi
how can i select data that contains in [ ] in sql column.
e.g.i have a column that contains large data in that col i have some data as –
employee name is [emp_name] and job description is [job_desc].
and i want to select only [emp_name] using sql qry n display it in stringbuilder.
thanks

Hello, my doubt is can we use ltrim/rtrim on integers and date variables.
this works pretty well, but certain coding standards avoid the use of trimming integer or date variables. Can you please suggest why so?

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.