Does SQL Server automatically trim nvarchar fields upon query?

entity-framework-6 tsql

Question

I have a question:

select '[' + p.Firstname + ']' from Person p
where p.Firstname = 'Johanne'

There are other people with this first name in the table, and some of them have a trailing space after the value (bad insertion of the values, it will be corrected).

Why then does this query return the following information (I added the brackets to show the spaces):

[Johanne]
[Johanne ]
[Johanne ]
[Johanne]

Could this be a configuration issue? The actual query, which is used in this example, is from entity framework version 6. How can I stop it?

thank you

I was able to make it work by utilizing EF6 and theSystem.Data.Entity.SqlServer.SqlFunctions.DataLength a process like this:

ctx.Person.FirstOrDefault(p => p.FirstName == "Johanne" && SqlFunctions.DataLength(p.FirstName) == "Johanne".Length);
1
4
2/24/2015 2:03:49 PM

Accepted Answer

When comparing strings with spaces, SQL Server adheres to the ANSI/ISO SQL-92 specification (Section 8.2, General rules #3). In order to ensure that the character strings used in comparisons have the same length before being compared, the ANSI standard mandates padding. The padding has a direct impact on other Transact-SQL string comparisons as well as the semantics of the WHERE and HAVING clause predicates. Transact-SQL, for instance, regards the strings "abc" and "abc" as being equal for the majority of comparison operations.

The LIKE predicate is the only one that deviates from this rule. The two values are not padded to the same length before the comparison when the right side of a LIKE predicate expression contains a value with a trailing space. This does not infringe on the earlier-mentioned provision of the ANSI SQL-92 specification because the LIKE predicate's function, by definition, is to facilitate pattern searches rather than straightforward string equality testing.

Watch: zzz-13 zzz

3
2/23/2015 8:28:59 PM

Popular Answer

You may make a brand-new SQL view and map it to an EF object. When using EF, the view may include the length of the field(s) you know you want to match against in the select using DataLength, and you could filter on that in your where clause. As an alternative, you might develop a SQL Stored Procedure that does the appropriate comparison using LIKE without the use of wildcards, map it back to your code, and then call it from your Where statement in EF.


With View

Create View MyCustomView
AS
SELECT [column1,column2,etc], DATALENGTH(FirstName) AS FirstNameLength
FROM Person
GO

Your EF would then be:

Persons.Where(p => p.FirstNameLength == "Johanne".Length && p.FirstName == "Johanne");

using a stored procedure

CREATE PROCEDURE [dbo].[GetPersons]
firstName int = null
AS
BEGIN
SET NOCOUNT ON;
select [your fields here]
from persons
where FirstName like @firstName
END

Make sure your mapping is accurate in C#, then

this.Database.SqlQuery<Person>("GetPersons","Johanne");

Directly modifying the sql in the DbContext prior to execution is also supported by EF6. You could create some custom code that, in certain situations, substitutes the = with like, but it might be simpler to try the suggestions above first.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow