Search Smith

ColdFusion, SQL queries, and, of course, searching

ColdFusion Regular Expressions – REMatch() and REMatchNoCase()

Posted by David Faber on March 30, 2013

I’m embarrassed that I am only finding out about these two functions six years after they were introduced. I was using reFind() and looping over the len and pos arrays as recently as this month!

Posted in ColdFusion | Tagged: , , | Leave a Comment »

Oracle: SEO-Friendly URLs II

Posted by David Faber on March 15, 2013

I 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 this:

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

Since then, I’ve been looking into regular expressions quite a bit. One difficulty with the expression above is that multiple characters will be replaced with multiple hyphens, plus other punctuation will be ignored — they will simply be replaced with nothing. Another, perhaps more obvious difficulty is its complexity! A better approach would be the following:

SELECT id, title, description, pubdate, journal_name, author_name, num_reads
     , REGEXP_REPLACE(LOWER(title), '[^a-z0-9]+', '-') AS url_keyword
  FROM articles

This simply replaces consecutive non-alphanumeric characters with a single hyphen. Since Oracle supports the POSIX character classes in regular expressions, the above could also be written:

SELECT id, title, description, pubdate, journal_name, author_name, num_reads
     , REGEXP_REPLACE(LOWER(title), '[^[:alnum:]]+', '-') AS url_keyword
  FROM articles

where [[:alnum:]] is the POSIX character class for alphanumeric characters and [^[:alnum:]] is its negation. A third option, if you know your data has no underscores or want them to be preserved for some reason (and not transformed into hyphens) would be the following:

SELECT id, title, description, pubdate, journal_name, author_name, num_reads
     , REGEXP_REPLACE(LOWER(title), '\W+', '-') AS url_keyword
  FROM articles

Posted in Oracle, SQL | Tagged: , , , , , | Leave a Comment »

More Statistics

Posted by David Faber on March 15, 2013

Odd that my lack of blogging does not seem to have affected traffic on this site! I posted in April of last year that the site had reached 1,000 page views from 400 unique visitors; that was over approximately 3 months of operation. In the (almost a full) year since then, there have been over 7,000 page views from 3,400-plus unique visitors, despite the fact that I have written exactly two blog posts in that time span (one of them yesterday):

Unsurprisingly, my most popular post — with 2,000 page views! — is still my ColdFusion Solr Tutorial, written in February 2012. Hopefully it is still relevant even with the release of ColdFusion 10. On the surprising side, my second most popular post is the aforementioned post about SHA hashing in Oracle 10g. I wish I could take even some credit for the content of that post but all I really did was bring the word of another developer (Jakub Wartak) to the attention of this blog’s readers. I wish I could say that my posts on SQL have been as warmly received!

Posted in ColdFusion, Miscellany, Oracle, Solr | Tagged: , , | Leave a Comment »