Perform exact search honouring spaces

Question

I'm using SQL Server 2017 and my collation is SQL_LATIN1_GENERAL_CP1_CI_AS and ANSI_PADDING is default value (ON).

In my table, one of the columns is of type NVARCHAR(255) and one of the values is inserted like this (including space):

N'abc '

And when I search it without space (N'abc'), I don't want to get N'abc ', but it finds it.

I know I can remove spaces during inserting record, but can't change already inserted records.

How can I prevent to find it with querying like this?

CREATE TABLE #tmp (c1 nvarchar(255))

INSERT INTO #tmp 
VALUES (N'abc ')

SELECT * 
FROM #tmp 
WHERE c1 = N'abc'

DROP TABLE #tmp

I also found this article but want to prevent while when I querying it.

I'm using Linq-to-entities with C#, and with SQL query, I can search with 'LIKE' keyword without percent character

SELECT * 
FROM #tmp 
WHERE c1 LIKE N'abc'

But with Linq, I don't know how to write this query:

entity.Temp.Where(p => p.c1 == "abc");
entity.Temp.Where(p => p.c1.Equals("abc"));
entity.Temp.Where(p => p.c1.Contains("abc"));

Sorry, i didn't describe my situation more about LinqToEntity, so I added it. — sugy21, Mar 08 '19 at 00:52

score 0 · Answer 1 · answered Mar 08 '19 at 03:37

0

You can try:

SELECT * FROM #tmp WHERE cast(c1 as varbinary(510)) = cast(N'abc' as varbinary(510))

This would be very slow if you have a lot of rows, but it works.

answered Mar 08 '19 at 03:37

David Dubois

Hi David. Thanks for suggestion. but "SELECT * FROM #tmp WHERE c1 LIKE N'abc'" works what I want, but what really I need is how can I express this for Linq-to-entity – sugy21 Mar 11 '19 at 06:53

1 Answers1