all spaces in a CHAR(5) column

i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?FROM BOL:

"Interpretation of an empty string is controlled by the compatibility level, which is set with the sp_dbcmptlevel system stored procedure. If the compatibility level is 65 or lower, SQL Server interprets empty strings as single spaces. If the compatibility level is 70 or 80, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel."

I never used this and afterreading the documentation for sp_dbcmptlevel I am not sure it is such a good idea.|||thanks, that at least sounds somewhat related

but we aren't talking about inserting an empty string

i even tried this --

insert into testzeros(myfield) values (space(5))

and this was converted to empty string as well|||aha!! found it!!

i had declared it as NULL

BOL says:If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column. If ANSI_PADDING is OFF when a char NULL column is created, it behaves like a varchar column with ANSI_PADDING set OFF: trailing blanks are truncated.
i am an idiot

:)|||Idiocy has been copyrighted??|||Idiots don't find the solutions to their own problems. Only an idiot would not know this.

Where that leaves you, I'm not sure. But thanks for posting the solution anyway.|||Idiocy has been copyrighted??no, but that particular phrase is all mine -- and it's a trademark!!

i lied about it being registered, though, and i suppose somebody else will eventually run out and register it -- i guess i'm just an idiot!!|||I did'nt know this off the top of my head. So I must be pretty stupid.

I am not participating in this forum anymore. Logging out.|||Try such. Its perversion IMHO, but one works
create table testzeros
( id smallint not null primary key identity
, myfield varchar(5) <--
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
, len(replace(myfield, ' ', '_')) <-- look this result
from testzeros|||no, but that particular phrase is all mine -- and it's a trademark!!

Ahh. That may explain why we have to go around saying "I R dum".|||"I R Dum" is freely available as SharePhrase. You can use it all you want, but you are not allowed to modify it or sell it.|||I'm thinking that with the mountains of easily verifiable "prior art" on this topic, that it would never survive a trademark registration. I could be wrong, but I think this would be a real pig to try to register. ;)

-PatP|||... the mountains of easily verifiable "prior art" on this topic... tee hee

i'm responsible for creating plenty of piles, that's for sure ;)