Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: True variable length string?

    What is the datatype for variable length string?

    If I declare a column as VARCHAR(30), and I supply the value "hello", it will still take up 30 bytes in the database as: "Hello " <-- I.e. padded with blanks such that the length is 30.

    What is the datatype for true variable length string? So that if I supply "hello" then it will only use "hello" and not pad it

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That would be VARCHAR for true, variable length strings.

    You're confusing presentation (what the client app shows) versus storage (what the database engine uses). The presentation is derived from the storage, but that's a cause-and-effect relationship, not one-to-one.

    -PatP

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Pat Phelan
    That would be VARCHAR for true, variable length strings.

    You're confusing presentation (what the client app shows) versus storage (what the database engine uses). The presentation is derived from the storage, but that's a cause-and-effect relationship, not one-to-one.

    -PatP
    Okay, so to confirm.... In the database, it's actually stored as "hello" but when the client app receives it, the db will automatically pad it to "hello "

    OR...

    Are you saying to use VARCHAR instead of VARCHAR(30)

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dbguyfh
    If I declare a column as VARCHAR(30), and I supply the value "hello", it will still take up 30 bytes in the database as: "Hello " <-- I.e. padded with blanks such that the length is 30.
    If that is the case you probably used the CHAR datatype, not the VARCHAR datatype.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The client app (Query Analyzer, SSMS, etc) is what does the padding, but otherwise you are correct. The database stored "Hello" as seven bytes (two for the length, five for data). The client displays it as thirty ("Hello" followed by twenty five spaces).

    -PatP

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Pat Phelan
    The client app (Query Analyzer, SSMS, etc) is what does the padding, but otherwise you are correct. The database stored "Hello" as seven bytes (two for the length, five for data). The client displays it as thirty ("Hello" followed by twenty five spaces).

    -PatP
    Ahhh great stuff! Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SQL Server does not store the padding for varchar data.

    You can try SELECT '[' + YourValue + ']' FROM YOURTABLE to see that this is so.

    If you are seeing padding in your client application, then somewhere your client app is treating it like a fixed length string.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    I'm running into a scuffle here

    To correct for this problem I return myValue.Trim()

    However, if myValue = "The quick brown fox " (notice the space at the end of fox) it becomes "The quick brown fox" (notice the space is trimmed).

    I don't want this behaviour!

    However, if I DON'T trim it, then I run into the obvious problem of having something like: "Hello "

    Isn't there a way to tell .NET not to pad strings??

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure exactly where your problem is, but SQL Server and .NET are not likely suspects. Neither .NET nor SQL Server pad VARCHAR or NVARCHAR columns.

    If you are showing a trailing blank, then either there was a trailing blank stored in the database or one was added somewhere along the line... If you put a Trim(myVar) value into a column, you'll retrieve a value with no trailing spaces when your code gets the value from the .NET framework.

    -PatP

  10. #10
    Join Date
    Jan 2008
    Posts
    186
    The thing is... The string I pass to the database needs to have a space at the end. That is -- not a padded string, but just a string with a space at the end.

    Just pretend that you are taking a substring of "The quick brown fox jumps"

    Now, if you take a substring at position 4, you'll get the value "The " -- with the space at the end, which is expected.

    So then I insert that into my database, and try to retrieve it, I end up with "The.....................".

    Naturally, I just put a Trim() on that string, however, now I end up with "The" -- without its original trailing space.

    So that's my delimma. I just need to get the string as it was originally stored in the database -- which is "The " (with the trailing space). If I don't call Trim() I end up with "The............." and if I DO call trim then I end up with "The" (without the original trailing space)

  11. #11
    Join Date
    Jan 2008
    Posts
    186
    By the way, if I run a direct INSERT statement from Manager Studio (not even my App, but directly from the manager studio), then after run a SELECT query, I get the same behaviour.

    So: INSERT INTO myTable ( 'hello ' )

    and then I do SELECT * FROM myTable

    and then copy one of the column values into notepad, it shows up as "hello................." (where the .'s are blank spaces)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you post DML and DDL for what you describe in post #11 because something ain't right here sir....

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    I see what you are trying to do. However, it is not a good approach to try to save trailing spaces in a database. If you need a space after the word "The", you should add it after you retrieve it. The space is not significant. It is not data. It is like storing the "(" and "-" characters in a phone number.

  14. #14
    Join Date
    Jan 2008
    Posts
    186
    Here is the code. No idea what's wrong
    Code:
    CREATE TABLE test_table3(
    	table_id int identity(1,1),
    	my_name char(30) not null,
    	primary key (table_id),
    )
    go
    insert into test_table3 values ('hello');
    go
    select * from test_table3

  15. #15
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by cascred
    I see what you are trying to do. However, it is not a good approach to try to save trailing spaces in a database. If you need a space after the word "The", you should add it after you retrieve it. The space is not significant. It is not data. It is like storing the "(" and "-" characters in a phone number.
    yeah, I see what you're saying. The reason I needed it was because my test cases take a substring from a larger string, insert it into the database (processing and all that happens etc.) then retrieve it again, and verify the original string matches what I put 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
  •