Link to home
Start Free TrialLog in
Avatar of Nakul Vachhrajani
Nakul VachhrajaniFlag for India

asked on

Why does SQL Server not pull expected results when using strings with comparison operators?

The question is based on an observation I had while working on something else. It is not stopping any production or development research work, but is more about satisfying my thirst for knowing why such a behaviour is being seen.

We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default: SQL_Latin1_General_CP1_CI_AS):

USE AdventureWorks2012 ;
GO

--Returns 5 records
SELECT  pp.Name
FROM    Production.Product AS pp
WHERE   pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO

Open in new window


The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".

User generated image
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.

USE AdventureWorks2012 ;
GO

--Returns 8 records
SELECT  pp.Name
FROM    Production.Product AS pp
WHERE   pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO

Open in new window


User generated image
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
ASKER CERTIFIED SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nakul Vachhrajani

ASKER

I see your point now. "Sport-100 Helmet" is actually > "Sport" and hence fails the check for <= "Sport".

Thank-you for the quick response!