Why White Space Inserted? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why White Space Inserted?

Author

Topic

SQLGuruji
Starting Member

6 Posts

Posted - 2009-03-08 : 17:35:55

Hello,

I populated a table in Db, using queries like the following but in the column "ctry" a white space is inserted.

INSERT INTO data (ip_from,ip_to,ctry,ctry_name) values (1078434272,1078434303,'GR','Greece')
INSERT INTO data (ip_from,ip_to,ctry,ctry_name) values (1078434304,1078434759,'US','United States')
INSERT INTO data (ip_from,ip_to,ctry,ctry_name) values (1078434760,1078434767,'BE','Belgium')
INSERT INTO data (ip_from,ip_to,ctry,ctry_name) values (1078434768,1078434775,'US','United States')

Now when I am fetching the rows it is showing the data from the column "ctry" with a space at the end, like "US ".

Can anyone tell me why is this white space inserted and how to get rid of it?

Thanks.

SQLGuruji
Starting Member

6 Posts

Posted - 2009-03-08 : 17:37:31

The structure of the table is as follows:

CREATE TABLE data
(
ip_from BIGINT NOT NULL,
ip_to BIGINT NOT NULL,
ctry CHAR(5) NULL,
ctry_name VARCHAR(80) NULL,
);

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-03-08 : 18:58:38

use varchar(5) instead of char(5) for ctry column.

elsasoft.org

SQLGuruji
Starting Member

6 Posts

Posted - 2009-03-08 : 19:13:32

quote:
Originally posted by jezemine

use varchar(5) instead of char(5) for ctry column.

elsasoft.org

Hello jezemine, thanks for the reply. I altered the column specs by following statement which corrected the problem:

ALTER table data modify ctry char(2);

Actually the column 'ctry' only two letter country codes and therefore, I thought there is no need for char(5).

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-03-09 : 00:08:28

Altering the table might have solved problem at ur end.

But i m still wondering, how it was inserting a blank space at the end.

Regards,
Sumit Batra
Software Engineer

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 00:22:46

try like this
CREATE TABLE data1
(
ip_from BIGINT NOT NULL,
ip_to BIGINT NOT NULL,
ctry CHAR(5) NULL,
ctry_name VARCHAR(80) NULL,
);

INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434272,1078434303,'GR','Greece')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434304,1078434759,'US','United States')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434760,1078434767,'BE','Belgium')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434768,1078434775,'US','United States')

select len(ctry),datalength(ctry) from data1

alter table data1 alter column ctry varchar(6)

INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434272,1078434303,'GR','Greece')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434304,1078434759,'US','United States')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434760,1078434767,'BE','Belgium')
INSERT INTO data1 (ip_from,ip_to,ctry,ctry_name) values (1078434768,1078434775,'US','United States')

select len(ctry),datalength(ctry) from data1

drop table data1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources