Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Unanswered: 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?
    Code:
    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 testzeros
    no 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ©®™

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Idiocy has been copyrighted??

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCrowley
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    I gezz I b n idit

    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    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

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by r937
    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".

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    ... the mountains of easily verifiable "prior art" on this topic...
    tee hee

    i'm responsible for creating plenty of piles, that's for sure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •