Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: String function
"Max" <massimo.panero_at_ideainformatica.org> wrote in message
news:bifarn$8hvkb$1_at_ID-121437.news.uni-berlin.de...
> 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
>
>
Max
This may help you.
SELECT
NVL (
LENGTH (
TRANSLATE (
REPLACE ('THIS IS A STRING', 'IS', '|'), '|ABCDEFGHIJKLMNOPQRSTUVWXYZ ', '|')), 0) THE_COUNTFROM DUAL; This assumes all text is uppercase or spaces without any pipe characters.
eric
-- Remove the dross to contact me directlyReceived on Tue Aug 26 2003 - 10:42:35 CDT