Re: Culturally aware initcap

I wrote this little utility function that someone might find useful:

CREATE OR REPLACE FUNCTION cul_initcap(input_val text) RETURNS text AS
$function_body$
-- culturally aware initcap, written by Peter Geoghegan ("sternocera")
-- use this in place of initcap, and strings with possessive
apostrophes are correctly initcap'd
-- (e.g. "PETER'S string" becomes "Peter's String", not "Peter'S String")
-- It still initcaps Irish names like O'Shaughnessy correctly
SELECT regexp_replace(initcap($1), $$'S([^[:upper:][:lower:]]|$)$$,
$$'s\1$$, 'g');

$function_body$
LANGUAGE 'sql' IMMUTABLE;

I'm going to put it on the wiki, because it's a problem that's come up
a good few times on IRC, and it ought to have a template solution.

Regards,
Peter Geoghegan

On 20/04/2010 00:06, Peter Geoghegan wrote:
> I wrote this little utility function that someone might find useful:
>
> CREATE OR REPLACE FUNCTION cul_initcap(input_val text) RETURNS text AS
> $function_body$
> -- culturally aware initcap, written by Peter Geoghegan ("sternocera")
> -- use this in place of initcap, and strings with possessive
> apostrophes are correctly initcap'd
> -- (e.g. "PETER'S string" becomes "Peter's String", not "Peter'S String")
> -- It still initcaps Irish names like O'Shaughnessy correctly
> SELECT regexp_replace(initcap($1), $$'S([^[:upper:][:lower:]]|$)$$,
> $$'s\1$$, 'g');
>
> $function_body$
> LANGUAGE 'sql' IMMUTABLE;
>
> I'm going to put it on the wiki, because it's a problem that's come up
> a good few times on IRC, and it ought to have a template solution.

Nice one Peter.

...Now what about Mc, Mac, Ní, Nic, Uí, etc etc etc? :-D

Actually, I had to do something similar recently in PHP using the Zend
framework; I did it as a filter on the input field of the relevant form.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

> Nice one Peter.
>
> ...Now what about Mc, Mac, Ní, Nic, Uí, etc etc etc? :-D

Well, they're people who don't have any reasonable expectation of
having the second syllable of their names initcap'd, because they
never have :-)

"culturally aware initcap" is actually a big misnomer. It just doesn't
step on the toes of O'Sullivans and O'Shaughnessys, who have come to
expect having their names initcap'd, while correctly initcap'ing
possessive apostrophes (i.e. not doing it).

Regards,
Peter Geoghegan