get results in alphanumric order, but letters first then numbers and other char-

[jasonc]

How would I get all results returned to be in order by letter then numbers after and then the other characters.

 

i.e.

 

apple

bet

coat

dig

014

156

467

987

.abc

 

and so on...

 

[AyKay47]

where are these result coming from? a database? array?

[jasonc]

sorry from mysql

[AyKay47]

use the ORDER BY clause on the end of your query and specify the row that you want to order your results by

[jasonc]

I have the order by already but it orders the list by numbers and then letters.

 

But I am wanting to have it ordered by letters first then numbers then other characters.

[AyKay47]

Note: this is untested..

 

SELECT * FROM table_name ORDER BY LEFT(col_name,1) > 0, left(col_name,1), substring(col_name,2) > 0

[jasonc]

Note: this is untested..

 

SELECT * FROM table_name ORDER BY LEFT(col_name,1) > 0, left(col_name,1), substring(col_name,2) > 0

 

I just tried this query and seems to work, only thing is that it shows like so

 

0 (zero)

a

b

c

d

...

1

2

3

4

[jasonc]

how would I get it to show...

 

a

b

c

d

...

0 (zero)

1

2

3

4

[fenway]

You can use the boolean result of REGEXP.

[jasonc]

i searched 'REGEXP' and looked to find it on php.net/REGEXP  but can find out how i do this ?

[fenway]

No, MYSQL's REGEXP -- you can determine if your values are all digits or not.

[jasonc]

looking at mysql these are the entries...

 

0.5L Cliptop Jar

0.5L Kilner Preserve Jar

Animal Ice Packs 3 Character Cells

Ant Killer Gel

Accents Brushed Stainless Steel 2 Slice Toaster

 

This is how they show, this is only a snippet of my data of course, but just to show where it is going wrong.

 

 

 

[fenway]

Well, you can order by if the first character is alpha or digit.