How do I count number of characters?

[lesstjm]

If I have a string "server1/server2/server3". Is there a function that can count the number of forward slashes "/" ?

[PAVB]

Probably not the most elegant but, it's functional...

SQL> 
SQL> select  CHR, CNT
  2  from (
  3  select  SUBSTR(mystr , level , 1) "CHR", 
  4          count(*)                  "CNT"
  5  from   (select 'server1/server2/server3' as mystr from dual) 
  6  connect by prior mystr = mystr 
  7  and level <= LENGTH(mystr) 
  8  and prior dbms_random.string ('p', 10) is not null 
  9  group by SUBSTR(mystr , level , 1) 
 10  order by SUBSTR(mystr , level , 1)
 11  )
 12  where CHR = '/'
 13  ; 

C        CNT
- ----------
/          2

SQL>

[hrishy]

Hi

if you are lucky and are on 11g then regexp_count also might be worth a try.

for a 10g solution try the one below
http://www.sqlsnippets.com/en/topic-12818.html

in 9i and below you should be still be able to use instr perhaphs

regards

[LKBrwn_DBA]

How about the time tested alternative:

SQL> SELECT Length('server1/server2/server3')
  2>      - Length(Replace('server1/server2/server3','/',''))
  3>   FROM dual;

LENGTH(:X)-LENGTH(REPLACE(:X,'/',''))
-------------------------------------
                                    2

SQL>

[PAVB]

How about the time tested alternative

clever!

[gandolf989]

That really is clever!!!

SYS AS SYSDBA> SELECT         Length('server1/server2/server3/server4/server1/server2/server3/server4/')
     - Length(Replace('server1/server2/server3/server4/server1/server2/server3/server4/','/','')) counter
  FROM dual;
  2    3  
   COUNTER
----------
         8

Elapsed: 00:00:00.02