Search Smith

ColdFusion, SQL queries, and, of course, searching

Posts Tagged ‘SQL’

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 »

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 »

Oracle 10g Pagination

Posted by David Faber on February 28, 2012

A colleague of mine alerted me to the following example of pagination in Oracle 10g:

select * from (
    select /*+ FIRST_ROWS(n) */
          a.*
        , ROWNUM rnum
        , count(*) over() as total_rows
    from ( 
        /* your select statement goes here */ 
    ) a
) b 
where b.rnum >= :start_row and b.rnum <= :end_row

This does have the advantage of working with an arbitrary SQL SELECT statement, but there is a somewhat simpler way of going about it if you know in advance what the SELECT statement will be:

SELECT * FROM (
    SELECT your_columns, ROW_NUMBER() OVER (ORDER BY your_sort) AS rnum
         , COUNT(*) OVER() AS total_rows
      FROM your_table
     ORDER BY your_sort
) WHERE rnum >= :start_row AND rnum <= :end_row

The key is that the value of your_sort must be the same in both places.

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