Tuesday, 19 February 2013

Find no of commas in a given string?


Using below 2 methods we can find out how many no of commas exists in a given string.
  
SQL> SELECT   TRIM (LENGTH ('PE,ME,TYPE,DATE,END'))
            - TRIM (LENGTH (TRANSLATE ('PE,ME,TYPE,DATE,END', 'A,', 'A'))) str_length
         FROM  dual;

     str_length
     ------------
     4

 SQL> SELECT   TRIM (LENGTH ('PE,ME,TYPE,DATE,END'))
             - TRIM (LENGTH (replace ('PE,ME,TYPE,DATE,END', ','))) str_length
        FROM dual;

     str_length
     ------------
     4