String comparison in SQL Server 2008

Question

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.

Answer 1

<= 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()

Answer 2

In what context? <= works in a SELECT statement.