How to sort string as number?
How To Convert String to Numeric for Sorting?
Dealing with embedded sort orders in SQL
from the comments, come a good one:
SELECT*
FROM TestSort
ORDER BY CONVERT(int,
SUBSTRING(CodeToSort,2,
PATINDEX('%[^0-9]%',
SUBSTRING(CodeToSort,2,200)+'|')-1))
PATINDEX: returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
RIGHT
|
not sort a10z after a10x
so
SELECT *
FROM TestSort
ORDER BY CONVERT(int,
SUBSTRING(CodeToSort,2,
PATINDEX('%[^0-9]%',
SUBSTRING(CodeToSort,2,200)+'|')-1)),
RIGHT(CodeToSort,
PATINDEX('%[0-9]%',
REVERSE(CodeToSort))-1)
This entry was posted
on Saturday, May 10, 2008 at 7:28 PM.
You can skip to the end and leave a response.
0 Comments:
Post a Comment