Reputation: 257
Does SQL Server 2008 have a string comparison method that checks which string is supposed to come first (ex 'abc' comes before 'abd' etc)? I need to do a <= comparison.
Upvotes: 0
Views: 3349
Reputation: 50282
<=
works fine. The problem you're having is that you're expecting numeric sorting out of strings. That doesn't work without special handling.
String Sorting
a1 - a10 strings sort in this order:
a1
a10
a2
a3
a4
...
This is because both a1 and a10 start with "a1".
Since they're strings the numeric values are irrelevant. Look what happens when we substitute a-z for 0-9:
ab
aba
ac
ad
ae
Can you see now why you're getting the results you are? In a dictionary, aba comes before ac, and a10 comes before a2.
To solve your problem it's best to split your column into two: one char and one a number. Some unpleasant expressions can get the right sort order for you, but it's a much worse solution unless you have absolutely no choice.
Here's one way. It may not suit or there may be a more efficient way, but I don't know what all your data is like.
SELECT
FROM Table
WHERE
Col LIKE 'a%'
AND Substring(Col, Convert(int, PatIndex('%[^a-z]%', Col + '0'), 1000)) <= 10
If the alpha part is always one character you can do it more simply. If the numbers can have letters after them then more twiddling is needed.
You could also try a derived table that splits the column into its separate alpha and numeric parts, then put conditions in the outer query.
Collation
Be aware each string and char-based column has a collation setting that determines what letters are sorted together (mostly for case and accents) and this can change the results of an inequality operation.
SELECT *
FROM Table
WHERE Value <= 'abc'
SELECT CASE WHEN Value <= 'abc' COLLATE Latin1_General_CS_AS_KS_WS THEN 1 ELSE 0 END
FROM Table
The collation I used there is case sensitive, accent sensitive.
You can see all the collations available to you like so:
SELECT *
FROM ::fn_helpcollations()
Upvotes: 4