Sorting on an alphanumeric field

[enchant]

One of the fields in a table I have is alphanumeric. THe values will be something like this:

MM8
MM9
MM10
MM11
...

Is there a way for me to sort on that field within sql, or do I need to extract the data into a perl array and futz with it there?

Obviously, I don't want the sort to look like this:

MM1
MM10
MM11
...
MM19
MM2
MM20
...

Thanks for any help.

[PineSolPirate]

I had a similar problem a ways back, I never found a good way to sort it.
I ended up writing a little program in PHP to sort it, but thats long gone.

[chazzy]

your best bet would be to separate the two items, the letters then the #'s in your case since there's a clear separation. you would then have as your order by:

ORDER BY alpha_column, number_column

[enchant]

Do you mean create two fields out of the one within the table?

[chazzy]

yes.

[russell]

what db r u using? what u want isn't too tricky in some, more difficult in others

[chazzy]

the only thing i can think of, other than what i suggested, is using substring() to break it, but you would need to know how many letters. not sure that we know that here. it might make more sense to have them as 2 separate fields. what are you thinking russell?

[russell]

was thinking substring and charindex with either an ascii check or isnumeric() if db is mssql.

msaccess queries can use pretty much all of the string functions of vbscript. Oracle and MySQL (as u know) I'm not familiar enough to attempt in SQL so I'd break into 2 fields as u suggest, or do in client code.

[russell]

we're making this too hard.

If it is ALWAYS MM###

then the query is easy

assuming the table is called myTable and the field is called v

SELECT 	Cast(Right(v, len(v)-2) as int) as num
FROM	myTable
Order By
	num

that's MSSQL code. There are equivalent casting functions in all dbs

[chazzy]

i don't think mysql has a cast function...and i don't think it's always MM### i think it's A+#+ (one or more characters, one or more numbers) at least that's my impression.

[enchant]

Last night before I went to bed (and I go to bed a lot earlier than you guys) I modified my application and added an extra numeric sorting field to the table. It strips out the alpha characters and stuffs the remaining numeric into that field. Then I sort on the numeric field.

However, that's some interesting stuff that you posted, and I'd like to play with it a little. Thanks for the help!

[chazzy]

to satisfy my curiosity, is it A+#+ or MM##?