How to sort varchar in SQL?

Question

In SQL ( using Access ), I have following values in a column:

Approved, Point 2, ..., Point 10, Point 11, ..., Point 21

Now, if I used order by column asc, Point 11 comes before Point 2. I want Point 2, Point 3 to come before Point 11. How to do it?

Answer 1

If you know that it's they're always going to be in the form "name number", what you can do is add two columns that are a split that original column , and sort on them instead of the original

e.g.,

SELECT foo2.foo, 
    Left(foo,InStr(foo," ")) AS foo_name, 
    CLng(IIf(InStr(foo," ")>0, Right(nz(foo,0),
            Len(nz(foo,0))-InStr(nz(foo,0)," ")),"0")) AS foo_number
FROM foo2
ORDER BY Left(foo,InStr(foo," ")), 
    CLng(IIf(InStr(foo," ")>0, Right(nz(foo,0),
            Len(nz(foo,0))-InStr(nz(foo,0)," ")),"0"));

(coded AND tested)

This should give you results like:

foo       foo_name  foo_number
---       --------  ----------
Approved  Approved  
Point 2   Point     2
Point 10  Point     10
Point 11  Point     11
Point 21  Point     21

and the sorting will work with the foo_number portion.

Answer 2

I tested this and it seems that Access is "smart" enough to know what you want. This is the minimum you need to do.

SELECT YourFields
FROM YourTable
ORDER BY 
   PointColumn,
   Mid([PointColumn],6)

This approach and others like it aren't SARGable so if you want to filter for records < Point 10 it will be slow.

So instead I recommend that you normalize your data. Add a field called IsApproved (boolean) and Add another field called point that keeps track of the points

Then its easy to do things like

SELECT IIF(IsApproved, "Approved", "Point " & [Point]) as output
FROM 
    table
WHERE
    IsApproved = true or Point < 10
ORDER BY
  IsApproved,
  Point