Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insertion of blank caracs in each rows.

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 …
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
Viewers will learn how the fundamental information of how to create a table.

718 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