Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: [varchar] (100) and empty space

    Hello

    I am creating the following table

    CREATE TABLE [dbo].[myT] (
    [name] [varchar] (100) NULL
    )


    if I do

    INSERT INTO myT (name) VALUES ('A')

    i get in the table a column with

    A ...

    the 100 char place is full even if there is a data with only 1 char

    how is it possible to avoid it ?
    I want 100 char maximum but not full with nothing


    thank you for helping

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you get when you run this?:

    select len([Name]), '[' + [Name] + ']' from myT

    Name is a reserved word, and so it is not a good label for a column, but I don't think this would cause the problem you are seeing.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anselme
    the 100 char place is full even if there is a data with only 1 char
    How do you know?

    Could this be a display issue of whatever client program you use to display the data?
    Only CHAR columns are padded to the full length not VARCHAR columns

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    I know it when I fill a formula with datas I am getting 99 empty spaces

    blindman it is an exemple i have no column named [name]

    if i run select datalength(name) from myT
    i am getting 200 2 times (100)
    Last edited by anselme; 05-22-07 at 13:43.

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Strange I just ran all the scripts above & everything looks good to me

    I get 1 from select datalength(name) from myT

    anselme

    take a deep breath reboot and start running these scripts again

    If this does'nt work

    Reply here and

    stop using the word Char as in
    the 100 char place is full even if there is a data with only 1 char
    explain what query editor you are using

    explain what database you are using

    run the script exactly as blindman suggests
    select len([Name]), '[' + [Name] + ']' from myT
    and tell us exactly the output

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by anselme
    blindman it is an exemple i have no column named [name]
    You have some sort of typo, and if you expect any more help on this you need to post the actual code so we don't waste more of our time.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by anselme
    I know it when I fill a formula with datas I am getting 99 empty spaces
    SQL Server does not have "formulas" to be "filled" (whatever that should mean).
    What exactly are you doing?

  8. #8
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    i agree with Gwilliy everything looks cool for me too.....
    varchar will only occupy the required number of space....
    however if still problems persist you can always use LTRIM and RTRIM to get rid of the remaining whitespaces

    so your query will be something like
    select LTRIM(RTRIM([name])) from myT

    thats the most we can get you.....

    However the fact that the 100 varchar place is full still mystifies me

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by nick.ncs
    However the fact that the 100 varchar place is full still mystifies me
    I suspect this is a front end issue.
    What is this "formula" thing he is filling in?

Posting Permissions

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