Oracle: Friendly URLs
Posted by David Faber on January 3, 2012
On the one hand, we want to avoid hitting the database if we don’t have to. On the other hand, if we can use built-in functions in our queries, this can be preferable to manipulating the data after it’s been returned from the database. For example, writing this:
SELECT emp_id, INITCAP(fname) AS fname, INITCAP(lname) AS lname FROM emp
is certainly* better than writing this:
SELECT emp_id, fname, lname FROM emp
and then using your development tool to capitalize the names after the data have already been returned (in CF it would look something like this: ucase(left(fname, 1)) & lcase(mid(fname, 2, len(fname) – 1)) – clunky!!).
*As long as your database queries need not be portable.
A better example still would be the following:
SELECT emp_id, COALESCE(dept_id, 0) AS dept_id FROM emp
instead of:
SELECT emp_id, dept_id FROM emp
With the latter I have to check to see if dept_id is non-null before displaying, while with the former I can simply display as-is.
What does this have to do with friendly URLs? Friendly as in SEO-friendly. The URL is very important in search engine optimization. Suppose we have a table of articles as in a previous post:
SELECT id, title, description, pubdate, journal_name, author_name, num_reads FROM articles
We would like to have the article’s title in the URL. The conventional wisdom is to use hyphens (-
) as word separators. We also don’t want any weird characters that are going to be url-encoded. The solution (or, at least, “a” solution) is to use regular expressions to get rid of unusual characters and replace spaces with hyphens. And here Oracle is truly our friend:
SELECT id, title, description, pubdate, journal_name, author_name, num_reads , REGEXP_REPLACE(REGEXP_REPLACE(LOWER(title), '[^-a-z0-9/ ]', ''), '[/ ]', '-') AS url_keyword FROM articles
The inner REGEXP_REPLACE replaces every character that is not (a) alphanumeric, (b) a space, or (c) a hyphen or forward slash with a null string. The outer one replaces forward slashes and spaces with hyphens. What is left over is bunches of alphanumeric characters (hopefully, words) separated by hyphens – in other words, an SEO-friendly URL string.
» Oracle: SEO-Friendly URLs II – Search Smith said
[…] posted over a year ago about how one might use regular expressions in Oracle to return SEO-friendly URLs in queries. The query I came up with at the time looked something like […]