Reply-To: <JB@BlackSkyTech.com>
From: "Jonathan Brinkman" <JB@BlackSkyTech.com>
To: <mgould@intermodalsoftwaresolutions.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Proper case function
Date: Thu, 23 Sep 2010 10:13:05 -0400
Organization: BlackSky Technologies, Inc.
Message-ID: <000b01cb5b29$715d0760$54171620$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: ActbKSehapEq6XuGSKuyT6zFWOcg6A==
Content-Language: en-us

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;