Is there a easy way to convert strings in a Sentence Case on Oracle 8? Or should I use regex?

Question

I know that in Oracle some string functions like UPPER, LOWER, INITCAP. But I need just the first letter of the sentence (or phrase) in uppercase, all other letters in lowercase, considering that words are delimited by white space or characters that are not alphanumeric and the sentence delimited by a punctuation mark.

So, convert this:

PEDIDO CANCELADO, DEVIDO AO ENCERRAMENTO DE INVESTIMENTO. SERÁ GERADA UMA NOVA REQUISIÇÃO PARA REGULARIZAR ESTA QUESTÃO.

to

Pedido cancelado, devido ao encerramento de investimento. Será gerada uma nova requisição para regularizar esta questão.

Answer 1

Like only in Oracle 10g was introduced native functions and support for regular expressions in SQL and PL/SQL, an option that I used for solve my problem in plsql was creating a function:

create or replace function scase(s in varchar2) return varchar2 as
  s_len    number;  
  cur      char(2); /*char(2) for accented character */
  up       boolean;
  terminal char(10) := '.?!]';
  r        varchar2(32767);
begin

  s_len := length(trim(s));

  if s_len = 0 then
    return r;
  end if;

  r := r || UPPER(substr(s, 0, 1)); /*First character of sentence*/

  for i in 2 .. s_len loop

    cur := substr(s, i, 1);

    if up = TRUE then
      if cur = ' ' then 
        r := r || ' ';
      else
        r := r || Upper(trim(cur));
        up := FALSE;        
      end if;
    else
       if cur = ' ' then 
        r := r || ' ';
      else
        r := r || Lower(trim(cur));
      end if;
    end if;

    /*I have found a bug here(Oracle 8i): instr return 7 when is ' ' (blank space) */
    if instr(terminal, trim(cur)) between 1 and 6 then
      up := TRUE;      
    end if;
  end loop;

  return r;
exception
  when others then
    raise;
end;

Another option is create a class in java 2. The last release of Oracle 8i (8.1.7) in August 2000, supports java 2.

So, you can create the code in java, like that:

create or replace and compile java source named tosentencecase as
public class toSentenceCase
{
 public static String toSentenceCase(String s) {
       String r = "";
       if (s.length() == 0) {
           return r;
       }
       char c1 = s.charAt(0);
       r = r + Character.toUpperCase(c1);  /*First character of sentence*/

       boolean up = false;
       char[] terminal = {'.', '?', '!'};
       for (int i = 1; i < s.length(); i++) {
           char cur = s.charAt(i);
           if (up) {
               if (cur == ' ') {
                   r = r + cur;
               } else {
                   r = r +  Character.toUpperCase(cur);;
                   up = false;
               }
           } else {
               r = r +  Character.toLowerCase(cur);;
           }
           for (int j = 0; j < terminal.length; j++) {
               if (cur == terminal[j]) {
                   up = true;
                   break;
               }
           }
       }
       return r;
   }
}

And after create a function that call that code, like that:

CREATE OR REPLACE FUNCTION toSentenceCase (s IN STRING) RETURN STRING
AS LANGUAGE JAVA
NAME 'toSentenceCase.toSentenceCase (java.lang.String) return String';

After that just call like normal function:

Connected to Oracle8i Enterprise Edition Release 8.1.7.3.0 

SQL> Select toSentenceCase('PEDIDO CANCELADO, DEVIDO AO ENCERRAMENTO DE INVESTIMENTO. SERÁ GERADA UMA NOVA REQUISIÇÃO PARA REGULARIZAR ESTA QUESTÃO.')
  2    from dual;

TOSENTENCECASE('PEDIDOCANCELAD
--------------------------------------------------------------------------------
Pedido cancelado, devido ao encerramento de investimento. Será gerada uma nova r

SQL> 

PS: Using java 2 (embedded in Oracle 8i) I tried import java.util.regex.Matcher and import java.util.regex.Pattern but couldn't compile the code to use regex.