Search Smith

ColdFusion, SQL queries, and, of course, searching

Archive for the ‘SQL’ Category

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 »

SHA-256 Hash in Oracle 10g

Posted by David Faber on August 24, 2012

It’s been a while since I’ve posted, and I’m not certain when I will get back to posting regularly (sorry!), but I came across an issue yesterday and found a blog post that was too good not to bring to others’ attention. I was looking to hash a column in Oracle with the SHA-2 256-bit hash function but that is not available in Oracle 10g.

The key to accomplishing this is found in the ability to load Java classes into Oracle and create Oracle functions based on the methods of those classes.

vnull – SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO

Incidentally, according to this post on the Oracle forums, the DBMS_CRYPTO package is certainly included in all editions of Oracle 11g, not just Enterprise Edition. There is nothing in the Oracle licensing information that would suggest that DBMS_CRYPTO is not available on Standard Edition, Standard Edition One, etc.

Having these encryption algorithms available within the Oracle DBMS is certainly helpful as it allows data to be encrypted or hashed via database triggers and the like.

To test your SHA-256 hash results, try the following link: SHA-256 Cryptographic Hash Algorithm.

If SHA-256 is not strong enough for you, SHA-512 is also included in the GNU Crypto project.

Posted in Oracle, SQL | Tagged: , , , , , , , , , , | 2 Comments »

Oracle: Getting milliseconds past midnight

Posted by David Faber on March 8, 2012

Do a search for oracle milliseconds past midnight and chances are you’ll see results for how to retrieve seconds past midnight:

SELECT TO_NUMBER( TO_CHAR( CURRENT_DATE, 'SSSSS' ) )
  FROM dual;

Finding the number of milliseconds that have elapsed since midnight (why would one do this? Well, perhaps to seed a random number generator) is actually fairly simple – use CURRENT_TIMESTAMP in place of CURRENT_DATE/SYSDATE:

SELECT TO_NUMBER( TO_CHAR( CURRENT_TIMESTAMP, 'SSSSSxFF' ) ) * 1000
  FROM dual;

You multiply by 1,000 as the result is returned as SSSSS.sss, where SSSSS is the number of seconds that have elapsed since midnight and sss is the number of milliseconds that have elapsed since the last second.

Posted in Oracle, SQL | Tagged: , , | 1 Comment »