• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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".

String Search - Query 01 results - only 5 records
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


String Search - Query 02 results - Observe 8 records are fetched
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?
0
Nakul Vachhrajani
Asked:
Nakul Vachhrajani
1 Solution
 
SimonCommented:
>This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".

When using the comparison  <= 'Sport', the string 'Sport' is the last possible match, so other strings starting with 'Sport' will be outside the matched range e.g. 'Sport-a', 'Sporta', 'sport ' are all > 'Sport'.

If you had the exact values 'Short' and  'Sport' in your table they would  both be included in the query results.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaAuthor Commented:
I see your point now. "Sport-100 Helmet" is actually > "Sport" and hence fails the check for <= "Sport".

Thank-you for the quick response!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now