order by alphabetical before numerical

[lejon]

is this possible?? i can't find code on it anywhere, on the mysql site i couldn't find it. i have strings in a varchar row with stuff like 20edit and 40proceed and i want them to appear after the letters when using order by asc.

[gunman]

You could use union, first fetch from database all of the data which begins without number and after that all of the data which begins just with number.
Something like:

select \data\ from \table\ where substring(\data\, 1, 1) not in ('0','1'..'9') order by \data\ asc union select \data\ from \table\ where substring(\data\, 1, 1) in ('0','1'..'9') order by \data\ asc

[guelphdad]

assuming DATA is the name of your column you want to order by simply do the following:


select
columnA,
columnC,
data
from yourtablename
order by
data regexp '[0-9]', 0+data, data

[lejon]

thanks a ton guys :D

[lejon]

SELECT * FROM myvideos WHERE vidtitles REGEXP '[0-9]' UNION SELECT * FROM myvideos WHERE vidtitles REGEXP '[A-Za-z]' ORDER BY vidtitles

This works. Just wondering if my REGEXP is ok for my strings, the A-Za-z without a space in between.

@ Guelphdad - I couldn't get it to work using 0+data,data, I don't know why...I didn't want to use php substrings to pull the first char and match, trying to keep the sql as simple as possible.

[guelphdad]

your query involves a union and would thus be slower.

can you show the query you tried that is similar to mine?

[lejon]

select * from myvideos order by vidtitles regexp '[0-9]', 0+vidtitles, vidtitles

[lejon]

This does work...however I don't have that many records in my db to check how far it goes...

SELECT * FROM myvideos ORDER BY vidtitles REGEXP '[0-9]' DESC, vidtitles