Re: Proper case function

Here is a simple title-case function for Postgresql.

Best,
Jonathan

CREATE OR REPLACE FUNCTION "format_titlecase" (
"v_inputstring" varchar
)
RETURNS varchar AS
$body$
/*
select * from Format_TitleCase('MR DOG BREATH');
select * from Format_TitleCase('each word, mcclure of this string:shall be
transformed');
select * from Format_TitleCase(' EACH WORD HERE SHALL BE TRANSFORMED TOO
incl. mcdonald o''neil o''malley mcdervet');
select * from Format_TitleCase('mcclure and others');
select * from Format_TitleCase('J & B ART');
select * from Format_TitleCase('J&B ART');
select * from Format_TitleCase('J&B ART J & B ART this''s art''s house''s
problem''s 0''shay o''should work''s EACH WORD HERE SHALL BE TRANSFORMED
TOO incl. mcdonald o''neil o''malley mcdervet');
*/

DECLARE
v_Index INTEGER;
v_Char CHAR(1);
v_OutputString VARCHAR(4000);
SWV_InputString VARCHAR(4000);

BEGIN
SWV_InputString := v_InputString;
SWV_InputString := LTRIM(RTRIM(SWV_InputString)); --cures problem where
string starts with blank space
v_OutputString := LOWER(SWV_InputString);
v_Index := 1;
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,1,1)) from 1 for 1); -- replaces 1st char of
Output with uppercase of 1st char from Input
WHILE v_Index <= LENGTH(SWV_InputString) LOOP
v_Char := SUBSTR(SWV_InputString,v_Index,1); -- gets loop's working
character
IF v_Char IN('m','M','
',';',':','!','?',',','.','_','-','/','&','''','(',CHR(9)) then
--END4
IF v_Index+1 <= LENGTH(SWV_InputString) then
IF v_Char = '''' AND UPPER(SUBSTR(SWV_InputString,v_Index+1,1))
<> 'S' AND SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1) then -- if
the working char is an apost and the letter after that is not S
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
ELSE
IF v_Char = '&' then -- if the working char is an &
IF(SUBSTR(SWV_InputString,v_Index+1,1)) = ' ' then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
ELSE
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
END IF;
ELSE
IF UPPER(v_Char) != 'M' AND
(SUBSTR(SWV_InputString,v_Index+1,1) <> REPEAT(' ',1) AND
SUBSTR(SWV_InputString,v_Index+2,1) <> REPEAT(' ',1)) then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
END IF;
END IF;
END IF;

-- special case for handling "Mc" as
in McDonald
IF UPPER(v_Char) = 'M' AND
UPPER(SUBSTR(SWV_InputString,v_Index+1,1)) = 'C' then
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index,1)) from v_Index for 1);
--MAKES THE C LOWER
CASE.
v_OutputString := OVERLAY(v_OutputString placing
LOWER(SUBSTR(SWV_InputString,v_Index+1,1)) from v_Index+1 for 1);
-- makes the letter
after the C UPPER case
v_OutputString := OVERLAY(v_OutputString placing
UPPER(SUBSTR(SWV_InputString,v_Index+2,1)) from v_Index+2 for 1);
--WE TOOK CARE OF
THE CHAR AFTER THE C (we handled 2 letters instead of only 1 as usual), SO
WE NEED TO ADVANCE.
v_Index := v_Index+1;
END IF;
END IF;
END IF; --END3

v_Index := v_Index+1;
END LOOP; --END2

RETURN coalesce(v_OutputString,'');
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;