Here is a UDF we find useful for first order cleaning up names form
various sources. Not only does it try to case everything correctly, it
regularizes spacing to single occurrence.
N.B. 'go\n' id my statement separator.
drop function is3.TitleCase
go
create function is3.TitleCase(str_in varchar(4000))
returns varchar(4000)
deterministic
no external action
contains sql
begin atomic
declare str_out varchar(4000) default '';
declare len int;
declare pos int default 1;
declare proc_state int default 0;
/* state meaning
0 start of string
1 last character was a blank
2 last character was not blank
*/
declare onec char(1);
if(str_in) is null
then return null;
end if;
set len = length(str_in);
while(pos<=len) do
set onec = substr(str_in, pos, 1);
if onec=' '
then
if proc_state=2
then set str_out = str_out || ' ';
end if;
set proc_state = 1;
else
if proc_state=2
then set onec = lower(onec);
else set onec = upper(onec);
end if;
set str_out = str_out || onec;
set proc_state = 2;
end if;
set pos = pos+1;
end while;
return str_out;
end
go