Padding with trailing spaces 1

[dukeslater]

I need to export to a text file with specified column positions, meaning that I need to right pad the results with spaces. I don't recall any problems in the past doing this, but this time I'm not getting the proper results. For example all of these return the length value of 4:

create table testtable (chartest char(8),
vartest varchar(8))

insert into testtable values ('8888 ','8888 ')

select
len(chartest + replicate(' ',4)),
len(vartest + replicate(' ',4)),
len(cast(vartest As Nchar(15))),
len(cast(chartest As Nchar(15))),
len(chartest),
len(vartest)
from testtable

I'm wondering now if there is some system setting that determines how SQL Server handles trailing spaces, but I'm only finding options for null.

This returns a value of 8, which is what I would expect:

len(replicate(' ',4) + chartest)

Thanks in advance for your help.

 

[SQLDenis]

len always trims the spaces
look at this select len('8888 '),datalength('8888 ')

use datalength instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dukeslater]

As usual, I wish I'd posted this yesterday. Thanks!