Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insertion of blank caracs in each rows.

Posted on 1998-11-19
5
Medium Priority
?
230 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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

578 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