Why would SqlServer select statement select rows which match and rows which match and have trailing spaces

Question

I have a table created with:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [nvarchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I inserted data with this:

insert into TestFeature1 (id,leng) values ('1',100);
insert into TestFeature1 (id,leng) values ('1 ',1000);

When I select from the table with this:

select * from TestFeature1 where id='1';

this returns 2 rows

1   100
1   1000

why would this be? why is it returning the version with the space on the end even when the query specifies that the value is only a 1 on its own, with no space?

FYI, if you put a primary key on [Id] your second insert will fail as a duplicate — KM., Jul 17 '09 at 13:59

polyglot · Answer 1 · 2009-07-17T14:06:06.173

3

To rework my answer, LEN() is unsafe to test ANSI_PADDING as it is defined to return the length excluding trailing spaces, and DATALENGTH() is preferable as AdaTheDev says.

What is interesting is that ANSI_PADDING is an insertion-time setting, and that it is honoured for VARCHAR but not for NVARCHAR.

Secondly, if returning a column with trailing spaces, or using the '=' for equality, there seems to be an implicit truncation of trailing space that occurs.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [varchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO

insert into TestFeature1 (id,leng) values ('1',100); insert into TestFeature1 (id,leng) values ('1 ',1000);

-- verify no spaces inserted at end
select '['+id+']', * from TestFeature1
select datalength(id), * from TestFeature1
go

DROP TABLE [dbo].[TestFeature1]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [nvarchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO

insert into TestFeature1 (id,leng) values ('1',100); insert into TestFeature1 (id,leng) values ('1 ',1000);

-- verify spaces inserted at end, and ANSI_PADDING OFF was not honoured by NVARCHAR
select '['+id+']', * from TestFeature1
select datalength(id), * from TestFeature1
go

edited Jul 17 '09 at 14:06

answered Jul 17 '09 at 13:37

polyglot

2,031
2
20
28

They don't truncate trailing spaces. e.g. try: SELECT '[' + id + ']', * FROM @Data where id = '1' that will show the space is still there - rather LEN() is doing a trim – AdaTheDev Jul 17 '09 at 13:40
I belive that the ANSI_PADDING statement controls the truncation of trailing spaces on insert. The varchar/nvarchar indicates if the text is unicode or not – Sam Holder Jul 17 '09 at 13:43
If you use DATALENGTH() instead of LEN(), that also shows it - lengths will be 2 and 4 (nvarchar=2 bytes per character) – AdaTheDev Jul 17 '09 at 13:43
The result of the query is like you said, but when I copy and paste the result into a text editor, I can see that one row contains '1' and the other contains '1 '. – Sam Holder Jul 17 '09 at 13:48
Actually AdaTheDev is correct, I'm quite wrong. LEN() is strictly defined to return the length excluding trailing spaces. What is interesting is NVARCHAR will always append spaces, irrespective of ANSI_PADDING. It is only VARCHAR which works with ANSI_PADDING it seems. – polyglot Jul 17 '09 at 13:54

AdaTheDev · Accepted Answer · 2009-07-17T14:12:23.967

Interestingly, works if you use LIKE:

select * from TestFeature1 where id LIKE '1'

Edit: after a bit more research I found others have had the same conversation as us. See here. That particular comment is half way through the discussion. But the outcome was as we have found, either use LIKE as demonstrated above, or add a 2nd condition to check the DATALENGTH of the column and supplied value are the same. I prefer the LIKE route.

score 1 · Answer 3 · answered Jul 17 '09 at 17:48

I really don't like a ID to be such a large variable length column, and I'd try to avoid that. However, if you really need this to work, add a computed column to your table where you prefix and suffix the column with pipes like:

ALTER TABLE TestFeature1 ADD
    IDx  AS '|'+ID+'|' PERSISTED

This will force the entire string to be compared to each other, since there will never be any leading or trailing characters. You can index this too.

you'd use code like this:

select * from TestFeature1 where id='|1|';
select * from TestFeature1 where id='|1 |';

score 0 · Answer 4 · answered Jul 17 '09 at 14:23

0

Look up this thread. Makes for a very interesting read about this topic.

Raj

answered Jul 17 '09 at 14:23

Raj

10,653
2
45
52

Can you inline important elements of that thread into your answer? As it stands right now, this answer probably better serves as a comment. – KyleMit Jul 12 '18 at 20:04

Why would SqlServer select statement select rows which match and rows which match and have trailing spaces

4 Answers4

Linked

Related