Carterhost Posted January 24, 2007 Share Posted January 24, 2007 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] Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/ Share on other sites More sharing options...
obsidian Posted January 24, 2007 Share Posted January 24, 2007 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.htmlhttp://dev.mysql.com/doc/refman/5.0/en/regexp.html Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/#findComment-168326 Share on other sites More sharing options...
fenway Posted January 24, 2007 Share Posted January 24, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/#findComment-168493 Share on other sites More sharing options...
Carterhost Posted January 24, 2007 Author Share Posted January 24, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/#findComment-168516 Share on other sites More sharing options...
effigy Posted January 24, 2007 Share Posted January 24, 2007 You could also use string functions, but a new column would be best for every day use--right fenway? Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/#findComment-168525 Share on other sites More sharing options...
fenway Posted January 25, 2007 Share Posted January 25, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/35554-order-by-regex/#findComment-169049 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.