Sorting VARCHAR Numerically 1

[blasterstudios]

I have a field setup in my database that is a varchar. It stores the information similar to:

Method 1
Method 10
Method 11
Method 100
Method 2
Method 200

I need to be able to strip down those numbers and sort my records on a php page to display numerically. So they should end up:

Method 1
Method 2
Method 10
Method 11
Method 100
Method 200

Is there a way to do this in a query?

 

[blasterstudios]

Sorry, I guess I should mention some records are like:

Method 1A
Method 1B

So it wouldn't do me any good to strip off the Method in the database and change the field to an INT.

 

[r937]

dontcha just love when mysql supports something that is egregiously non-standard (such as allowing you to add a number and a string) which just happens to solve something you need to do...

create table test_orderby
( id tinyint not null primary key auto_increment
, charfield varchar(37)
);
insert into test_orderby (charfield) 
values
 ('Method 1')
,('Method 10')
,('Method 11')
,('Method 100')
,('Method 2')
,('Method 200')
,('Method 1A')
,('Method 1B')
;
select * from test_orderby 
order by charfield
;
[b]results[/b]
id charfield
 1 Method 1
 2 Method 10
 4 Method 100
 3 Method 11
 7 Method 1A
 8 Method 1B
 5 Method 2
 6 Method 200

select *
     , 0+substring(charfield,8) as c  
  from test_orderby
order 
    by 0+substring(charfield,8)
     , charfield
    
[b]results[/b]
id charfield    c
 1 Method 1     1
 7 Method 1A    1
 8 Method 1B    1
 5 Method 2     2
 2 Method 10   10
 3 Method 11   11
 4 Method 100 100
 6 Method 200 200

r937.com | rudy.ca

 

[KarveR]

I like. *

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.