Geek asks and answers

How to sort varchar in SQL?

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

Approved, point 2, ..., point 10, point 11, ..., point 21

Now, if I used order column asc, point 11 goes to point 2. I want point 2, point 3 to be higher than point 11. How to do this?

+3
source share
5 answers

If you know that it will always be in the form of "name number", then you can add two columns, which are a section of the original column, and sort them instead of the original

eg.

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"));

(encoded and verified)

This should give you the following results:

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

and sorting will work with the foo_number part.

+3
source

, , Access "", , . , .

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

SARGable, , < Point 10, .

. IsApproved (boolean) , ,

, ,

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

, replace, - (, sql):

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

tblName - , pointColumnName - .

0

- :

  • varcharcol, intcol (varcharcol, "Point" intcol, )

  • Add an additional indexed column containing "Point 000001", "Point 000010", "Point 000020", etc. with enough zeros to accommodate what you need.

0
source

To be short..

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

More articles:


All Articles