[Resolved] ORDER BY numeric on varchar column

[cretaceous]

here's the query:

$result = mysql_query("SELECT u.u_id, u.ctr_id, u.mall_id, u.u_name, c.ctr_id, c.ctr_name, m.mall_id , m.mall_name
FROM shopunits as u, centres as c, malls AS m
WHERE u.ctr_id = c.ctr_id AND m.mall_id= u.mall_id AND u.ctr_id=$ctrid
ORDER BY m.mall_name, u.u_name desc", $db);

the problem is the second ORDER BY column 'u_name' contains things like :

Third floor kiosk
Unit 9
Unit 7
Unit 12
First floor cafe
Unit 1
Unit 32

All I want is for entries with the word "Unit" to come out at the top, sorted numerically (1,7,9,12 etc)

and everything else below that sorted by alpha order

The first part of my ORDER BY is ordering the items by Mall name and works as I want

At the moment I've tried ORD, LOCATE and nothing does what I want

Frustratingly ORD(u.u_name) gives me what I want but upside down - so I add a 'desc' and it goes haywire

(I don't really want to add another column because my users like to enter the entire name in one field)

[liquorvicar]

(I don't really want to add another column because my users like to enter the entire name in one field)

why don't you create a new column but let your users enter the entire name in one field? it's not too difficult to parse tje examples you have below and split them into 2 columns.

[Sxooter]

can you order by a regex in mysql?

[cretaceous]

I ended up splitting the column into u_name and u_num and then did this in the order by:

ORDER BY m.mall_name, u.u_name REGEXP '^(Unit)'desc , u.u_name, u.u_num

-- I tried a lot of other regexp queries prior to splitting the columns -
I think a UNION on two SELECTS with a mutually exclusive LIKE and NOT LIKE would have fixed it but the host site isn't on mysql 4

thx!