Sorting on an alphanumeric field
One of the fields in a table I have is alphanumeric. THe values will be something like this:
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:
Thanks for any help.
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.
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
Do you mean create two fields out of the one within the table?
what db r u using? what u want isn't too tricky in some, more difficult in others
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?
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.
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
that's MSSQL code. There are equivalent casting functions in all dbs
SELECT Cast(Right(v, len(v)-2) as int) as num
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.
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!
to satisfy my curiosity, is it A+#+ or MM##?
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)