Re: String function

From: andrewst <member14183_at_dbforums.com>
Date: Tue, 26 Aug 2003 06:53:42 -0400
Message-ID: <3284913.1061895222@dbforums.com>

Originally posted by Max

> Is there a function that return how many occurrence of a substring
> are in a

> string?

> Or how can i do by myself?

> Example:

> ST := 'THIS IS A STRING';

> N := XFunction ( ST, 'S' ); // return 3

> N := XFunction ( ST, 'IS' ); // return 2

> N := XFunction ( ST, 'X' ); // return 0

Have to write your own, e.g.

SQL> create function instrc( string in varchar2, pattern in varchar2 )

  2 return number

  3 is

  4 v_occurence pls_integer := 0;

  5 begin

  6 while instr( string, pattern, 1, v_occurence+1 ) > 0 loop

  7 v_occurence := v_occurence+1;

  8 end loop;

  9 return v_occurence;

 10 end;

 11 /

Function created.

SQL> select instrc('this is a string','s') from dual;

INSTRC('THISISASTRING','S')

---

                          3

SQL> select instrc('this is a string','is') from dual;

INSTRC('THISISASTRING','IS')

---

                           2

SQL> select instrc('this is a string','x') from dual;

INSTRC('THISISASTRING','X')

---

                          0

--
Posted via http://dbforums.com

Received on Tue Aug 26 2003 - 05:53:42 CDT