Customized INITCAP 1

[marjac]

I need to update columns to mixed case letters. I use INITCAP, which does fine at capitalizing the first letter in the word. The question I have is how to use proper capitalization. Example: MACDONALD is Macdonald instead of MacDonald. Does anybody know any rules to use or tools to use to do proper mixed case in Oracle?

 

[Turkbear]

Hi, It would be interesting if any such algorithmn or proc exists - it would be a real challenge to generalize a rule that would handle this kind of thing...

 

[JediDan]

I've always found in these cases that it's simply easier to store the correct value in the database instead of having to alter it with crazy rules and functions...

Even if you had to have two columns, one storing the UPPER() value, and one storing the normal value, I think that would be better than trying to design a function with all sorts of CASE statements in it.

Oh wait, this is the 8i forum, you can't have CASE statements anyway...


Good luck.

 

[SantaMufasa]

Actually, JediDan, CASE in SELECTs has been around since 8.1.x.

Cheers.

 

[marjac]

I agree that it would be easier to store the correct value in the database JediDan, but then I would need a way to train all the Monkey’s to do so .

 

[jad]

if you can work out the correct case once, then you can store the correct case and shouldn't then need to work it out on the fly.

I create a person string with the correct case that looks for 'Mc' in the name as in McDonald, but not Macdonald

i use a function that is called from a trigger to automatically set the correct case.

function Full_client_name (title in varchar2 default null,
                           firstname in varchar2 default null,
                           middlename in varchar2 default null,
                           surname in varchar2) return varchar2 is
    v_name varchar2(240) := '';
begin
    if (Title is not null) then
        v_name := nls_initcap(title) || ' ';
    end if;
    if (firstname is not null) then
        v_name := v_name || nls_initcap(firstname) || ' ';
    end if;
    if (middlename is not null) then
        v_name := v_name || nls_initcap(middlename) || ' ';
    end if;
    if (upper(surname) like 'MC%') then
        v_name := v_name || 'Mc' || nls_initcap(substr(surname,3, length(surname)));
    else
        v_name := v_name || nls_initcap(surname);
    end if;

    return v_name;
end full_client_name;

 

[Turkbear]

Hi,
If a generalized function ( not just for names) is written it would need to handle such stuff as:

Convert macintosh into MacIntosh , unless it is the Apple Computer brand, then leave it as is or make it Macintosh.

Handle a text field that is a sentence ( like a comment) like:
McDougal sold the macintosh to MacDonald's .

Silly examples but it shows that case conversion is very hard to generalize into a rule - even for Names.

 

[jad]

MacIntosh is wrong ... McIntosh would be right, Macintosh would be right.

Macdonalds shouldn't have the upper case

 

[Turkbear]

Hi,
See what I mean..

All generalizations are wrong, even this one....

 

[marjac]

Well...t’s been fun. I really needed to only worry about a last name field at this point, and jad has started me on writing a custom function...so thanks to all for an interesting thread!