CODEWITHSUNDEEP
sqlsql-server-2005filteringcollation
Pablo Venturino
Pablo Venturino

Reputation: 5318

SQL Server query brings unmatched data with BETWEEN filter

I'm querying on my products table for all products with code between a range of codes, and the result brings a row that should't be there.

This is my SQL query:

select prdcod
from products
where prdcod between 'F-DH1' and 'F-FMS'
order by prdcod

and the results of this query are:

F-DH1
F-DH2
F-DH3
FET-RAZ       <-- What is this value doing here!?
F-FMC
F-FML
F-FMS

How can this odd value make it's way into the query results?

PS: I get the same results if I use <= and >= instead of between.

Upvotes: 2

Views: 483

Answers (4)

Arvo
Arvo

Reputation: 10570

According to OP request promoted next comment to answer:

Seems like your collation excludes '-' sign - this way results make sense, FE is between FD and FM.

:)

Upvotes: 4

Michael
Michael

Reputation: 165

Try replacing the "-" with a space so the order is what you would expect:

DECLARE @list table(word varchar(50))

--create list
INSERT INTO @list
SELECT 'F-DH1'
UNION ALL
SELECT 'F-DH2'
UNION ALL
SELECT 'F-DH3'
UNION ALL
SELECT 'FET-RAZ'
UNION ALL
SELECT 'F-FMC'
UNION ALL
SELECT 'F-FML'
UNION ALL
SELECT 'F-FMS'

--original order
SELECT * FROM @list order by word

--show how order changes
SELECT *,replace(word,'-',' ') FROM @list order by replace(word,'-',' ')

--show between condition
SELECT * FROM @list where replace(word,'-',' ') between 'F DH1' and 'F FMS'

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

How about adding AND LEFT(prdcod, 2) = 'F-'?

Upvotes: 0

CodeMonkey1313
CodeMonkey1313

Reputation: 16011

between and >= and <= are primarily used for numeric operations (including dates). You're trying to use this for strings, which are difficult at best to determine how those operators will interpret the each string.

Now, while I think I understand your goal here, I'm not entirely sure it's possible using SQL Server queries. This may be some business logic (thanks to the product codes) that needs implemented in code. Something like the Entity Framework or Linq-to-SQL may be better suited to get you the data you're looking for.

Upvotes: 0

Related Questions