CODEWITHSUNDEEP
sqlms-accesssql-order-by
Salman Virk
Salman Virk

Reputation: 12307

How to sort varchar in SQL?

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?

Upvotes: 3

Views: 636

Answers (5)

BIBD
BIBD

Reputation: 15384

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.

Upvotes: 3

Conrad Frix
Conrad Frix

Reputation: 52655

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

Upvotes: 2

CResults
CResults

Reputation: 5105

For brevity..

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

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

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.

Upvotes: 0

ManiP
ManiP

Reputation: 743

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.

Upvotes: 0

Related Questions