How can I combine the LOCATE() and SUBSTR() functions in MySQL to create a new column?

I have a database table with following layout, containing 100.000 entries:

id | url 
1  | http://www.foo.com/ZedFe.htm
2  | www.foo.com/tEzqz.htm?q=eee
3  | foo.com/ZeeFg.htm
4  | http://www.foo.com/lkeiO
etc.

I now want to have a third column with t开发者_Python百科he 5-character code of every url, but as you see not every url is formatted in the same way. In php, I would do it like this:

$id = substr($url, strpos($url,'foo.com/') + 8, 5);

Can this be done in MySQL, using the LOCATE() and SUBSTR() functions?

Another I think more common point of view is that string operations are a perfectly legitimate part of SQL and using them for the purpose you describe is quite appropriate.

Here's the basic list - you'll find what you are familiar with - use CONCAT because there's no equivalent operator.

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html

I found many examples when I googled "mysql sql parse url". One interesting sample:

SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(LEADING
"https://" FROM TRIM(LEADING "http://" FROM TRIM(url))), "/", 1), ":",
1), ".", IF(url LIKE "%.org.__%" OR url LIKE "%.net.__%" OR url LIKE
"%.com.__%" OR url LIKE "%.__.us%" OR url LIKE "%.co.__%" OR url LIKE
"%.__.uk%", -3, -2) )

String manipulation in SQL is generally not a good idea. It's possible, but painful.

If you need this data regularly, then consider adding a new column to the table and pre-process the data to populate this field. Performance will be better too, because the new field can be indexed, and directly matched again.

Deriving the query value dynamically makes it impossible to optimize and you'd force a complete table scan for every query.