pink cylinders

More than Four

...there's an axis for that.

Implementing a Proper() case function in PL/SQL

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
l_string varchar2(200);
l_proper varchar2(200);
l_proper := '';
-- change any underscores to spaces:
l_string := replace(lower(p_string), '_',' ' );

for i in 1..length(l_string)
-- obviously the first character is UPPER:
if i =1 then
l_proper := l_proper || upper( substr( l_string, i, 1 ) );
-- 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 ) );
l_proper := l_proper || substr( l_string, i, 1 );
end if;
end if;
end loop;
return l_proper;

Comments (1) -

  • LSN

    10/13/2013 12:29:06 PM | Reply

    I'm curious: did you not find any steal-able examples, or just none that you deemed worthy?

    A lot of times you need to do the opposite -- take a "Proper-ized" column name without spaces or underscores between words, such as MyProperizedColumnName, and turn it into a phrase with spaces, suitable for default header captions when reporting.  It takes similarly simple code to do and a lot of products do this for you internally.  But I remember I did write my own, not finding one I liked.


Add comment