{"id":74,"date":"2012-01-03T23:58:24","date_gmt":"2012-01-04T04:58:24","guid":{"rendered":"http:\/\/www.thefaberfamily.org\/search-smith\/?p=74"},"modified":"2012-01-03T23:58:24","modified_gmt":"2012-01-04T04:58:24","slug":"oracle-friendly-urls","status":"publish","type":"post","link":"http:\/\/www.thefaberfamily.org\/search-smith\/2012\/01\/oracle-friendly-urls\/","title":{"rendered":"Oracle: Friendly URLs"},"content":{"rendered":"

On the one hand, we want to avoid hitting the database if we don’t have to<\/a>. 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:<\/p>\n

SELECT emp_id, INITCAP(fname) AS fname, INITCAP(lname) AS lname\r\n  FROM emp<\/pre>\n
is certainly* better than writing this:<\/p>\n
SELECT emp_id, fname, lname\r\n  FROM emp<\/pre>\n
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!!).<\/p>\n
*As long as your database queries need not be portable.<\/p>\n
A better example still would be the following:<\/p>\n
SELECT emp_id, COALESCE(dept_id, 0) AS dept_id\r\n  FROM emp<\/pre>\n
instead of:<\/p>\n
SELECT emp_id, dept_id\r\n  FROM emp<\/pre>\n
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.<\/p>\n
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<\/a>:<\/p>\n
SELECT id, title, description, pubdate, journal_name, author_name, num_reads\r\n  FROM articles<\/pre>\n
We would like to have the article’s title in the URL. The conventional wisdom is to use hyphens (-<\/code>) 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:<\/p>\n
SELECT id, title, description, pubdate, journal_name, author_name, num_reads\r\n\u00a0\u00a0\u00a0\u00a0 , REGEXP_REPLACE(REGEXP_REPLACE(LOWER(title), '[^-a-z0-9\/ ]', ''), '[\/ ]', '-') AS url_keyword\r\n  FROM articles<\/pre>\n
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<\/em>) separated by hyphens – in other words, an SEO-friendly URL string.<\/p>\n
\r\n      
\r\n        <\/g:plusone>\r\n      <\/div>\r\n    \r\n      
\r\n        Tweet<\/a>\r\n      <\/div>\r\n    \r\n      
\r\n        
<\/div>\r\n