It's been a while since my last blog entry, mainly caused by changes in my environment (internet access, job, workload), but now I'm back. In the next view days I'm going to review some of the ideas I had last years and hopefully I've got some time this year to build them.

However, my next project is going to be a APEX based WIKI. A quick look in my favorite search engine didn't show anything useful yet, and there's also no official packaged app from Oracle itself. I've started using a Wiki at a new project and grew quite fond of it - yet I hesitate to install an extra WIKI app when I've got APEX on my server already.

Improving on my debug/log framework for APEX is another topic on my to-do list, after taking a peek at LOG4PLSQL I decided to add a bit more logic to mine but keep it still simple (and hopfully slim).

Another (for me) interesting area is APEX authorisation, I think I'm going to write a little article about this and (maybe) come up with my own little framework there.

For what's it worth: My root server, where my XE instance and this blog is hosted, got rebooted today and my 300+ days uptime went down the drain. :-(

That's it for today ...

It started out as a question as how to use Use REGEXP_REPLACE as advanced initcap function? and turned into a little challenge on how to translate whole unformatted strings into properly initcap'd ones. I decided to play it "simple" by taking my solution for one word into a MODEL clause and simply split any given string into several words.

WITH t AS (SELECT 'FIRSTNAME O''MALLEY' col1
             FROM DUAL
            UNION
           SELECT 'FIRST''NAME  TEH''TE'
             FROM DUAL
            UNION
           SELECT 'FORMAT ME BYGGER''N'
             FROM DUAL
            UNION
           SELECT 'OLD MCDONALD'
             FROM DUAL
            UNION
           SELECT 'EVEN OL''DER MACDONALD'
             FROM DUAL)
SELECT col1
     , new_col1
  FROM t
 MODEL
 PARTITION BY (ROWNUM rn)
   DIMENSION BY (0 dim)
   MEASURES(col1, CAST('' AS VARCHAR2(255)) word, CAST('' AS VARCHAR(255)) new_col1)
   RULES ITERATE(99) UNTIL (word[0] IS NULL)
                (word[0] = REGEXP_SUBSTR(col1[0], '[^ ]+( *|$)', 1, ITERATION_NUMBER + 1)
               , new_col1[0] = new_col1[0]
              || INITCAP(REGEXP_SUBSTR(word[0], '^([^'']''|ma?c|.)', 1, 1, 'i'))
              || INITCAP(REGEXP_REPLACE(word[0], '(^([^'']''|ma?c)(.*)$)|^.*$', '\3', 1, 1, 'i'))
              || LOWER(REGEXP_REPLACE(word[0], '^(([^'']''|ma?c).*$|.)', '', 1, 1, 'i'))
                )
  ;
 
COL1                      NEW_COL1
------------------------- -------------------------
EVEN OL'DER MACDONALD     Even Ol'der MacDonald
FIRSTNAME O'MALLEY        Firstname O'Malley
FORMAT ME BYGGER'N        Format Me Bygger'n
FIRST'NAME  TEH'TE        First'name  Teh'te
OLD MCDONALD              Old McDonald