Trailing Spaces being automatically trimmed?

Question

Trailing Spaces being automatically trimmed?

Zaid-256406

Right there with Babe

Points: 791
More actions
September 28, 2005 at 1:40 pm

#102828

Hi Friends,
Does SQL Server/T-SQL automatically trim trailing spaces? I have a file that I have loaded through the Bulk Copy (bcp) command wherein some of the strings have a trailing space at the very end of them. I need to keep this space. However, when I run a query with the "len" length function, it is not counting the space.
Also, I have attempted to insert a record with the T-SQL "insert" statement that contained a space character (e.g., INSERT INTO TABLE...VALUES ('ZZZZ ') ) at the end of it. But again, when I queried the table, it ignored the space in the "len" command.
Is there a reason for this? I thought one would have to specify the "rtrim" function in order to get rid of spaces rather than have them automatically trimmed.
Any help would be greatly appreciated.
Thanks,
Zaid

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

If your field has a datatype of varchar, the spaces will be trimmed. It has to be a char field to keep any spaces you want/need.

Take a look at "Using char and varchar Data" in the Books Online(BOL)

Michelle

Zaid-256406 Right there with Babe Points: 791 More actions · Answer 2

Hi Michelle,

Thanks for the reply.

I just checked the table definition and I in fact created the field to be 'char'.

Thanks

Zaid-256406 Right there with Babe Points: 791 More actions · Answer 3

I was doing some research and I noticed that the ANSI_PADDING parameter treats trailing spaces differently whether it is on or off. So I attempted the following statement:

SET ANSI_PADDING ON - this was done in the regular Query Analyzer window.

However, when I perform the following statement to verify whether it is turned on:

SELECT DATABASEPROPERTYEX ('DATABASE1', 'ISANSIPADDINGENABLED') - this was also done in the regular window of Query Analyzer

the query returns a '0' which I am interpreting as being FALSE, or, in other words that ANSI_PADDING is off.

Could this ANSI_PADDING paramater be the source of my problems? And if so, why am I seemingly not able to change the parameter of this property? I do not have Admin access nor Enterprise Manager.

Any help would be tremendously appreciated!

Thanks in advance,

Zaid

john-132685 SSCertifiable Points: 6134 More actions · Answer 4

It may just be a misunderstanding of the "LEN" function:

create table #test ( a CHAR(10), b VARCHAR(10) )

insert into #test SELECT 'x ', 'x '

select len( a ) , len( b ) from #test

----------- -----------

1 1

(1 row(s) affected)

select datalength( a ) , datalength( b ) from #test

----------- -----------

10 6

(1 row(s) affected)

Ian Yates SSCoach Points: 19738 More actions · Answer 5

Set ANSI_PADDING is something that applies to the current block of SQL, it doesn't change it for the database. The databasePropertyEx function tells you the "default" value that connections will obtain when they connect to the DB. So ahead of your SQL code, you should place the set ANSI_PADDING ON statement.

I would also recommend that you download the 120day evaluation of SQL Server Enterprise and just install the client tools (one of the early screens in setup with 3 radio buttons, choose the tools only button) - will help you fiddle with your MSDE installation.

Look up the procedure sp_configure - it lets you set the database/server properties such as ANSI_PADDING. For all installations that I have, I run the following statement once on the server

exec sp_configure N'user options', 376

I cannot remember which options I am setting, but 376 is a bitmask field which sets a number of default settings so that indexed views work without throwing errors!

Hope that helps

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

A few points...

LEN(...) will always do a rtrim before it calculates the length. It's how the function works. If you retrieve the data you will see that the space is still there.

As John noted the function DATALENGTH will count the trailing spaces. From BoL 'Datelength - Returns the number of bytes used to represent any expression.'

Be careful if you use Datalangth on fields declared as NChar or NVarchar as for both of those types one character requires two byes for storage.

Varchar fields do not trim trailing spaces. If you insert a string 'a ' it will be stored as 'a '

Char fields pad out data with spaces to the size of the column. If you declare a column as char(10) and insert a string 'a' it will be stored as 'a '

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Zaid-256406 Right there with Babe Points: 791 More actions · Answer 7

Hi again folks, thank you for your kind replies...

While the 'len' and 'datalength' differences are important for another part of my project, what I am trying to do is join two tables, so that only matched records are displayed. For example:

tblOne tblTwo

NamesOne NamesTwo

--------- --------

'zaid ' 'gilamonster is kind'

'john' 'gary is awesome'

'gilamonster' 'zaidisnotsogood'

'Larry ' 'johnishelpful'

And here is my join query:

SELECT tblOne.NamesOne, tblTwo.NamesTwo

FROM tblOne, tblTwo

where tblOne.NamesOne = tbltwo.NamesTwo;

I don't want the space character to be ignored. The results of this query and this data set that I would like to see is the following:

Results

-------

'john' 'johnishelpful'

'gilamonster' 'gilamonsteriskind'

In other words, 'Zaid ' should not match with 'Zaidisnotsogood' but as of now, that would create a match for me. I would like the query not to ignore the space character, but right now it is.

Thanks a lot for all your help folks! Its is much appreciated.

Zaid

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

SELECT tblOne.NamesOne, tblTwo.NamesTwo

FROM tblOne, tblTwo

where tbltwo.NamesTwo LIKE tblOne.NamesOne + '%'

This is assuming that you want the 1st name to be in the first part of the 2nd name.

Zaid-256406 Right there with Babe Points: 791 More actions · Answer 9

My bad...what I meant to type was:

SELECT tblOne.NameOne, tblTwo.NameTwo

FROM tblOne, tblTwo

WHERE tblOne.NameOne = left(tblTwo, len(tblOne))

But in this case, the len(tblOne) will trim off the last character from the tblOne.NameOne.

This means that it will evaluate 'Zaid ' as having 4 characters which will then match to 'Zaidisnotsogood'. RGR'us, I believe your elegant SQL example would have the same effect since it will still evaluate 'Zaid ' as 'Zaid'.

Thanks

Zaid

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

So did you try my query before telling me it's not working???

Zaid-256406 Right there with Babe Points: 791 More actions · Answer 11

Hi RGR'us

Yeah I did try your suggested query.

However, it did not work. After much testing, the query seems to have worked when switching fields from char to varchar. I'm going to do a little more testing to hopefully confirm that that is the case.

Thank you for all your help!

Zaid

Ian Yates SSCoach Points: 19738 More actions · Answer 12

Yes char fields will cause you grief - as someone above said, storing

'wxyz' in a char(10) field will always give you back a 10 character string such as

'wxyz______'. The varchar lets you store "up to" 10 characters - it is for this reason alone that I rarely use char fields just because I forget such things!!

I had read somewhere that there is a "break even" point for char vs varchar fields at about the 10 character mark. IE, if you are storing < 10 chars always, then it is more efficient to store as a char(10) - although there may be other overheads such as trimming the string for your joins. If you are storing, say, up to 25 characters, it is more efficient to store in a varchar(25) than a char(25).

So yes, RGR'us' example will work a treat IFF they are varchar fields - it will be consistent and "makes sense" - using the char field just confuses you, me and others I imagine