Re: PL/SQL Help

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 8 Mar 2002 21:39:32 -0800
Message-ID: <45a06b65.0203082139.1fef0fa6_at_posting.google.com>

"MA_IL" <aramraj1_at_yahoo.com> wrote

> : How to convert a string example "MyTable" to "MY_TABLE"?
 

> : The program should identify the capital letter in the string and prefix it
> : with an "_" (underscrore) and also capitalize others.

try this
martin_at_BUB> create or replace function str_tab_format (

  2      p_in_tabname in varchar2
  3      )
  4  return varchar2 is
  5      v_formatted_tab varchar2(30) := substr(p_in_tabname,1,1);
  6  begin
  7      for i in 2..length(trim(p_in_tabname)) loop
  8          if (ascii(substr(p_in_tabname,i,1)) > 64) and
  9              (ascii(substr(p_in_tabname,i,1)) < 91) then
 10              v_formatted_tab := v_formatted_tab || '_';
 11          end if;
 12          v_formatted_tab := v_formatted_tab || substr(p_in_tabname,i,1);
 13      end loop;
 14      return upper(v_formatted_tab);

 15* end;
 16 /

Function created.

martin_at_BUB> select str_tab_format('MyTable') from dual;

STR_TAB_FORMAT('MYTABLE')

---

MY_TABLE martin_at_BUB> select str_tab_format('TestTable') from dual;

STR_TAB_FORMAT('TESTTABLE')

---

TEST_TABLE martin_at_BUB> select str_tab_format('TestTableOne') from dual;

STR_TAB_FORMAT('TESTTABLEONE')

---

TEST_TABLE_ONE martin_at_BUB> select str_tab_format('TestTableTwo') from dual;

STR_TAB_FORMAT('TESTTABLETWO')

---

TEST_TABLE_TWO also see SQL and PL/SQL documentation at OTN e.g.

http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/toc.htm http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/toc.htm

free registration needed. Received on Sat Mar 09 2002 - 06:39:32 CET