RE:number of occurrences of a character in a string

The Oracle SQL*Plus reference guide covers this quite well.

select length(translate('jj#hh#rh#8867#99',
                                '#1234567890abcdefghijklmnopqrstuvwxyz',
                                '#'
                                )
                  ) occurences from dual;

does the trick quite nicely

Rgds
Allan
Allan.Webster_at_PanCredit.com

> ----------
> From: Matt Brennan[SMTP:mbrennan_at_gers.antispam.com]
> Posted At: Tuesday, August 04, 1998 11:21 PM
> Posted To: server
> Conversation: SQL Plus question - number of occurrences of a
> character in a string
> Subject: SQL Plus question - number of occurrences of a character
> in a string
>
> Is it possible to find out the number of occurrences of a character in
> a
> string? For example, if I have this character string...:
>
> jj#hh#rh#8867#99
>
> ...is there a function that will tell me how many times the pound sign
> occurs in this string?
>
> Such as:
>
> select <some_function_name_that _I_don't_know>('jj#hh#rh#8867#99','#')
> from
> dual;
>
> (I would want '4' returned to me when executing this.)
>
> Or, is there a way I can just strip out the other characters that
> aren't
> '#', returning '####', and then I can just do a length on '####',
> which
> essentially gets me to the same answer. (I couldn't get translate or
> replace to work for me there though.)
>
> Any help is appreciated - thanks in advance!
> --
> Matt Brennan
> SQL*Tools Specialist
> GERS Retail Systems
> 9725-C Scranton Road
> San Diego, California 92121
> 1-800-854-2263
> mbrennan_at_gers.com
> (Original email address is spam-blocked.)
>
Received on Wed Aug 05 1998 - 05:57:39 CDT