Search Smith

ColdFusion, SQL queries, and, of course, searching

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.

One Response to “Oracle: Friendly URLs”

  1. […] 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 […]

Leave a Comment

Your email address will not be published.