| SitePoint Sponsor |
|
|
User Tag List
Results 1 to 3 of 3
Thread: Sorting Alphanumeric+dots
-
Jun 3, 2003, 00:19 #1The Hiding One
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sorting Alphanumeric+dots
Hi,
I'm try to sort a column in mysql db that contains numbers, letters and dots.
Eg.
1.4.1a
1.2.3
1.2.30
1.4.5
It should sort to:
1.4.5
1.4.1a
1.2.30
1.2.3
How do i do that? A normal ORDER BY would not work with the .30 and .3
The column type is VARCHAR. I tried looking at the mysql doc, and some user comments suggested SOUNDEX and ORD but it doens't seem to work for me. Any ideas?
Thanks."Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 8, 2003, 04:42 #2The Hiding One
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Any ideas guys? Thanks
"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 8, 2003, 05:03 #3SQL Consultant
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 38,778
- Mentioned
- 49 Post(s)
- Tagged
- 1 Thread(s)
you could, i'm sure, write a series of nested functions that use LOCATE to find the periods and SUBSTR to pull out whatever's between two of them...
... but it would be quite nasty
if i were doing it, i'd just define three extra columns in the table, and put the sort values in there every time i add a new row
rudy
Bookmarks