Re: VARCHAR: Working to design, but why?

Fred Prose wrote: > > According to the SQL Reference manual: > > "When you store a VARCHAR calue in the database, only its defined characters > are stored. The database does not strip a VARCHAR object of any > user-entered trailing blanks, nor does the database server pad the VARCHAR > to the full length of the column." > > The question is, why would the design be that way -- ie. the trailing > blanks not stipped if the equivalent of "ABC " is entered. I've > never worked with a product that made that decision. Design decisions were made that's all. The reason for the particular design is that 1) CHAR padds to full length with spaces on retrieval and may pad on insert depending on the type of the HOST variable containing the data (data inserted from a fixchar host variable will not be space padded any trailing nulls in the string will be preserved in the data row); 2) The spaces trailing a value MAY be significant. Look at the three strings "ABC", "ABC ", & "ABC " to the server these are all the same because the server ignore trailing spaces when comparing character data, but to the application which stored and will retrieve and process the data these may be three unique strings and so the server enforces that by neither padding nor stripping the trailing spaces from a VARCHAR column. > A test case also showed that if you alter a table and change a column from > CHAR(20) to VARCHAR(20) you get the entire VARCHAR(20) padded. Correct because the CHAR column has already been padded on insert and the engine has no way to determine if the trailing spaces were originally significant or not. It therefore takes the safest approach and stores EXACTLY what it found a 20 character space padded string. Art S. Kagel