pink cylinders  

More Than Four

...there's an axis for that.

Implementing a Proper() case function in PL/SQL

by colin 2. October 2013 23:36

I found that I needed to convert some raw column names (i.e. THIS_IS_MY_COLUMN) into a human-readable "Proper" or "Title" case form, e.g. This Is My Column.

Scouring the web, I didn't find out I could steal, so here's my implementation:

   function Proper(
p_string varchar2
) return varchar2
is
l_string varchar2(200);
l_proper varchar2(200);
begin
l_proper := '';
-- change any underscores to spaces:
l_string := replace(lower(p_string), '_',' ' );

for i in 1..length(l_string)
loop
-- obviously the first character is UPPER:
if i =1 then
l_proper := l_proper || upper( substr( l_string, i, 1 ) );
else
-- if the character is preceded by a space, UPPER it:
if substr( l_string, i-1, 1 ) = ' ' then
l_proper := l_proper || upper( substr( l_string, i, 1 ) );
else
l_proper := l_proper || substr( l_string, i, 1 );
end if;
end if;
end loop;
return l_proper;
   end;
 
header bg