Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insertion of blank caracs in each rows.

Posted on 1998-11-19
5
Medium Priority
?
228 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
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:Victor 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 300 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

926 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