Re: Proper case function - Mailing list pgsql-sql

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_OutputStringplacing
 
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;