ORDER BY & regex

[Carterhost]

I've searched the oracle(Google) and come up with nothing, so I'm trying here:

Can you use regex within a SQL statement?

I have a table, with a column that contains a filename.

Can I order an SQL statement by the extension of the filename (could be .jpg, or .jpeg) without storing the extension as a separate column?

[code]SELECT * FROM files ORDER BY.....?[/code]

[obsidian]

The REGEXP operator in mysql, as best I can tell, will only give you a boolean match similar to a preg_match() call in PHP. You cannot return portions of the string, though. Based on reading extensively through the following two pages of the MySQL manual, I would venture to say that you won't be able to do what you're after in this case. Someone else, please correct me if I'm wrong, though. I've tried to come up with a way myself, to no avail.

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

[fenway]

obsidian is right, unfortunately -- it's just a match operator, and a poor one at that (no groupings returned, etc.).  However, what's wrong with the extra column?

[Carterhost]

Nothing, really, apart from now I have to write a little script to modify my DB contents instead of just adding a few chars to an SQL statement.

I was just curious as to whether there was an easier way!

[effigy]

You could also use string functions, but a new column would be best for every day use--right fenway?

[fenway]

Sorry, I've been unclear -- I meant an extra column in the SELECT column list (e.g. an expression), NOT actually adding a column to the table!