Why does the SqlServer select statement select strings that match and strings that match and have trailing spaces

Question

I have a table created using

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 a table with this:

 select * from TestFeature1 where id='1'; 

it returns 2 rows

 1 100 1 1000 

why was that? why does it return a version with space at the end, even when the request indicates that this value is only 1, without a space?

Answer 1

Interestingly, it works if you use LIKE:

 select * from TestFeature1 where id LIKE '1' 

Edit: After several additional studies, I found that others had the same conversation as ours. See here . This particular comment is halfway through the discussion. But the result was as we found, either use LIKE, as shown above, or add a second condition to check the DATALENGTH column and the given value. I prefer the LIKE route.

Answer 2

To rework my answer, LEN () is not safe to check ANSI_PADDING, since it is defined to return lengths, excluding trailing spaces, and DATALENGTH () is preferable, as AdaTheDev says.

Interestingly, ANSI_PADDING is setting the input time and that for VARCHAR this is done, but not for NVARCHAR.

Secondly, if you return a column with trailing spaces or use '=' for equality, it seems that an implicit truncation of the trailing space 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 

Answer 3

Check out this thread. Makes it very interesting to read about this topic.

Rajah

Answer 4

I really don't like the identifier, which should be such a large column of variable length, and I will try to avoid this. However, if you really need to do this, add the calculated column to your table, where you prefix and suffix the column with channels such as:

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

This will make the entire string compare with each other, as there will never be any leading or trailing characters. You can also index this.

you would use this code:

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