469,962 Members | 2,375 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,962 developers. It's quick & easy.

A recasing UDF

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
Jun 27 '06 #1
0 852

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.