March 1, 2015 at 11:15 am
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
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".
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
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?
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 1, 2015 at 12:33 pm
That's because Short-Sleeved is > Short, so it doesn't satisfy the <= 'Short' condition
March 1, 2015 at 1:15 pm
I see your point now. "Sport-100 Helmet" is actually > "Sport" and hence fails the check for <= "Sport".
Thank-you for the quick response!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 1, 2015 at 6:45 pm
What's wrong with this?
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name LIKE N'Short%'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply