Incorrect result set... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect result set...

Author

Topic

Madhav
Starting Member

38 Posts

Posted - 2009-03-31 : 06:30:04

Hi,
I am using sqlserver 2005.
I am getting incorrect result set when the nvarchar column has trailing spaces.
CREATE TABLE test1(id numeric(10,0), name nvarchar(20) )
insert into test1 values(1,'DBA')
insert into test1 values(2,'DBA ') --2 spaces
insert into test1 values(3,'DBA ') --3 spaces
insert into test1 values(4,'DBA ') --5 spaces
select len(name),* from test1 where name = N'DBA'
--The above select should return 1 row. But Im getting 4 records.
Is there any setting at DB level?
Please help me.

--Madhav

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 07:03:24

why do you think it should return you one row?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 07:08:35

use LIKE instead of = to treat trailing space as actual data.

select len(name),* from test1 where name like N'DBA' and name not like N'DBA '

http://support.microsoft.com/kb/316626

Madhav
Starting Member

38 Posts

Posted - 2009-03-31 : 07:15:20

quote:
Originally posted by sakets_2000

why do you think it should return you one row?

Because I am searching for the string 'DBA'. In this case there is only one value in the table test1 where name equal to 'DBA'. Remaining 3 have space characters at the end. So in the where condition, I am searching for exact match of 'DBA'. so I should get only one record.

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-03-31 : 07:29:23

Its because your INSERT INTO statement is ignoring the spaces at the end of the string and is just inserting 'DBA'. When you SELECT * WHERE name = 'DBA' you get four records because there are four exact matches.

That's the problem, maybe someone else can provide a solution although I can't see a reason in the real-world why you would actively want trailing blanks at the end of a field.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 07:33:17

The spaces are not ignored by INSERT they are inserted, but they are ignored by select when using equals, however the LIKE does not ignore trailing spaces.

Madhav
Starting Member

38 Posts

Posted - 2009-03-31 : 07:48:27

quote:
Originally posted by theboyholty

Its because your INSERT INTO statement is ignoring the spaces at the end of the string and is just inserting 'DBA'. When you SELECT * WHERE name = 'DBA' you get four records because there are four exact matches.

That's the problem, maybe someone else can provide a solution although I can't see a reason in the real-world why you would actively want trailing blanks at the end of a field.

I clearly mentioned in the insert statements. I am inserting space characters follwed by DBA string.
First insert has no trailing spaces.
Second inset has 2, third has 3 and fourth insert has 5 trailing space characters.
insert into test1 values(1,'DBA') --No trailing spaces.
insert into test1 values(2,'DBA ') --2 spaces
insert into test1 values(3,'DBA ') --3 spaces
insert into test1 values(4,'DBA ') --5 spaces
[Note: This webpage not showing all trailing space chars. It is showing only one trailing space character even if typed 2,3,5 space characters in the above insert statement]
For the above inserts,
If I query the table like
select len(name), name from test1 where name = 'DBA'
--It should return 3, DBA as result set
--But I am getting Four records with length = 3 and name = DBA

If I query the table like
select len(name), name from test1 where name like 'DBA%'
--I should get 4 records as
3 DBA
5 DBA
6 DBA
8 DBA
-- But I am getting 4 records as
3 DBA
3 DBA
3 DBA
3 DBA

My quetion is, when I am inserting trailing spaces, why the length of the value is not chaging, and when i query the table using "where name ='DBA'" Why Im getting four records?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:21:10

well, len function doesn't account for trailing spaces. It just counts the number of char in the string. I think what you need is DATALENGTH.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:22:36

you should also read set SET ANSI_PADDING ON while inserting into your tables incase you want trailing spaces to be preserved.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:27:19

here, try running these and see the difference..


set SET ANSI_PADDING ON
--
CREATE TABLE test1(id numeric(10,0), name varchar(20) )
insert into test1 values(1,'DBA')
insert into test1 values(2,'DBA ') --2 spaces
insert into test1 values(3,'DBA   ') --3 spaces
insert into test1 values(4,'DBA     ') --5 spaces
--
select len=datalength(name),* from test1 where name ='DBA' 
select len=len(name),* from test1 where name ='DBA'

Madhav
Starting Member

38 Posts

Posted - 2009-03-31 : 08:57:07

quote:

Originally posted by sakets_2000

here, try running these and see the difference..


set SET ANSI_PADDING ON
--
CREATE TABLE test1(id numeric(10,0), name varchar(20) )
insert into test1 values(1,'DBA')
insert into test1 values(2,'DBA ') --2 spaces
insert into test1 values(3,'DBA   ') --3 spaces
insert into test1 values(4,'DBA     ') --5 spaces
--
select len=datalength(name),* from test1 where name ='DBA' 
select len=len(name),* from test1 where name ='DBA'

I used the datalength function and I can see the correct length of NAME column values.
But still my question is not answered. When I use where condtion as "Where name ='DBA'"(With out any trailing spaces), Why I am getting 4 records? I used ANSI_PADDING ON.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 09:10:31

because sql server doesn't take into account trailing spaces while doing string comparisons. setting ANSI_PADDING just affects storage. Let me find you a BOL link on this.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 09:11:32

here you go, http://support.microsoft.com/kb/316626

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 09:13:29

use LIKE 'dba' - not LIKE 'dba%'
if you use the wildcard "%" its the same in this case as using equals.

select datlength(name),* from test1 where name like N'DBA' and name not like N'DBA '

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 09:15:26

quote:
Originally posted by sakets_2000

here you go, http://support.microsoft.com/kb/316626

I posted that link a while ago.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources