How to sort varchar in SQL?

In SQL ( using Access ), I have following values in a colum开发者_如何转开发n:

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?

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.

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

Not sure about access but if there is a replace function, then you could do something like this(like in sql server):

select      *, cast ( replace( replace( pointColumnName, 'Point', ''), 'Approved', 1000) as int )  as points
from        tblName
order       by points

where tblName is the table and pointColumnName is the column with your data.

With respect to performance, the usual tricks are two:

1. Sort using varcharcol, intcol (varcharcol containing "Point" and intcol containing the number)

2. Add an extra indexed column that contains "Point 000001", "Point 000010", "Point 000020", etc. with enough zeros to accommodate what you need.

For brevity..

SELECT * From MyTable
Order By Int(Replace(MyColumn,'Point',''))