Solved

Insertion of blank caracs in each rows.

Posted on 1998-11-19
5
225 Views
Last Modified: 2010-03-19
Hi,

I DO am a novice user of SQLServer 6.5 and 7.0 beta3
and I have a problem in my DB that is the following:

MSSQL automatically inserts white spaces at the end of each
new slot in any inserted row so that the size of each string in the row is maximal.

For instance, if I create a table with a column of type
VARCHAR(5) and I insert a row containing the string "toto", the table will contain a row with the string
equaling "toto ".

The strange thing is that it does not happend systematically!
If I drop the table and fill it several times, I do not
get the same problem again, but new rows inserted
afterwards are still affected...

This should not be coming from my stored procedures as long
as I haven't got any pb on another DB...

Is there a problem with my SQLServer configuration?
0
Comment
Question by:grosbill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:formula
ID: 1091904
I do not get the same result as you.  I tried the exact same example and although it looks like white space in the varchar(5)
field (when doing select *), in actuality the 5th char is null.
I tested this by trying this select:

select substring(field1,5,1) from test1

This select returns a null in column 5 of the field which is correct.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091905
This will be the case if you use CHAR , not  VARCHAR. What do you use to view the table?
0
 
LVL 1

Expert Comment

by:csalves
ID: 1091906
Hi,
Are you sure that your problem is not in INSERT?
if it is use trim$() function to insert your data.

bye,
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1091907
SQL Server does not "Pad" varchar columns to fill them all out.  YOu need to look outside of the server for the problem if this is the result you are getting, such as at the program that loads the rows, or the program you are using to display the rows.  You can verify this yorself by using an insert query within SQL Enterprise manager.  It will not pad the the columns.  Something else is doing this to your app, if it is really happening.
0
 

Author Comment

by:grosbill
ID: 1091908
OK, I was all wrong and my question was wrong too because I discovered that I had
some CHAR(...) left in some stored procedures instead of VARCHAR(...).

So lots of thanks to you all (and especially spiridonov) for your help!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question