Page 1 of 1
sorting varchar elements
Posted: Thu Aug 12, 2004 12:21 am
by bugthefixer
i have a fiel named station_code whose datatype is varchar
most of the values are numerical only a few are aplhabetical like a-1 a2 etc.
now i want to sort it in asc order..i have tried but it sorts lik this
1
10
100
11
111
2
21
212
but it shud sort like this
1
2
10
11
21
and so on
anybody cud tell me how to solve this problem
the query i m using is this
Code: Select all
<?php
mysql_query("select train_name,train_code,direction from train where defined='Y' order by tain_code asc");
?>
Posted: Thu Aug 12, 2004 12:53 am
by feyd
I found this an interesting challenge, but here's a solution I found:
Code: Select all
SELECT *
FROM `train`
WHERE `train_code` REGEXP '^ї0-9]+$'
UNION(
SELECT * FROM `train`
WHERE `train_code` REGEXP 'ї^0-9]'
ORDER BY LENGTH( `train_code` ) , `train_code`
)
edit: ok, I thought it was working.. hmm
Posted: Thu Aug 12, 2004 1:09 am
by bugthefixer
well thanx feyd i have tried it but it gives error on UNION
anyhow i did manage to get required result from this
but it only displays numerical values
i want all the values to be printed
if the value is alpha-numeric then it shud be printed at end
Posted: Thu Aug 12, 2004 1:16 am
by feyd
you'll need a double query then, if you can't perform a union.. or a really oddly configured join.. lemme fiddle around..
Posted: Thu Aug 12, 2004 1:52 am
by feyd
ok.. here's two that don't use unions.. however, both require a bit of processing. The first, uses double the memory, but only requires you to check if a column is null. The other, you would need a small regex to determine if you want to display it first or second pass.
Code: Select all
SELECT a.`train_name` `train_name1`, a.`train_code` `train_code1`, a.`direction` `direction1`, b.`train_name` `train_name2`, b.`train_code` `train_code2`, b.`direction` `direction2`
FROM `train` a
LEFT JOIN `train` b
ON b.val REGEXP 'ї^0-9]'
AND b.id = a.id
ORDER BY CHAR_LENGTH(a.val), a.val
----------
SELECT a.`train_name`, a.`train_code`, a.`direction`
FROM `train` a
ORDER BY CHAR_LENGTH(a.`train_code`), a.`train_code`
I cannot get it to sort the way you'd like it to, without unions or double queries..
Posted: Fri Aug 13, 2004 12:53 am
by bugthefixer
thanx feyd..
Posted: Fri Aug 13, 2004 9:30 am
by pickle
The sorting method you're looking for is natural sort - which means to sort like a human would, not like a computer would. Since you're calling it from PHP, you can access the PHP function [php_man]natsort[/php_man]. Once you retrieve the data, put that column in an array, then natsort() it. It'll be a bit of work to get the other data sorted along with it, but I'm sure it's doable.