| INITCAP this! [2008.03.03] Bookmark (1.0.0.0) 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
This may not be the most maintainable code and if you'd want to use that logic for updates, it might be a better idea to write a PL/SQL function for this problem.
C. |