Order by string

[Phlashman]

I'm doing a website for a video shop and need to order the films by title.

The problem is films or TV series that have numbers over 10 don't show in order.

e.g.
film 1
film 10
film 11
film 2
film 3...

is there a way I can change the SQL 'order by' bit to something that'll sort it better?

[laserlight]

You could use PHP's [man]natsort/man, I'm not sure if there is an equivalent for SQL.

[drawmack]

Originally posted by Phlashman
I'm doing a website for a video shop and need to order the films by title.

The problem is films or TV series that have numbers over 10 don't show in order.

e.g.
film 1
film 10
film 11
film 2
film 3...

is there a way I can change the SQL 'order by' bit to something that'll sort it better?

sure they do 1 has a lower ascii value then 2 so 10 has a lower ascii value then 2 because of it's order. For your sort you should split the title into two columns
base name - string
episode - integer

then you sort by base_name.episode and it'll be beautiful

[lazzerous]

dood - just use leading zeros - everything will show in order then.

01
02
03
\/
08
09
10
11

[TheIceman5]

just use an id number