Can you count repetitions in a string? 2

[SmpTraining]

I have a VarChar2(366) field that contains a series of letters (such as 'AAAAAXXAAAABXXAAAAA' and so on.

Is there anyway I can count all the As, Bs and Cs using SQL or must I use PL/SQL?

Many thanks.
Steve

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[taupirho]

This might give you some pointers but it only partially answers your question

e.g count X's in the string below

1 select length('AABCghdururAur') - length(replace('AABCghdururAur','X')) cnt
2* from dual
SQL> /

CNT
----------
0

SQL>


Count A's now

1 select length('AABCghdururAur') - length(replace('AABCghdururAur','A')) cnt
2* from dual
SQL> /

CNT
----------
3

SQL>

 

[Thargy]

smp,

below is proof positive that it can be done.

SQL> set pagesize 80
SQL> SELECT CHR(LEVEL+64) letter,LENGTH('AABCghdururAur') -  LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+64))) cnt  FROM dual CONNECT BY LEVEL < 27
  2  UNION
  3  SELECT CHR(LEVEL+96) letter,LENGTH('AABCghdururAur') -  LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+96))) cnt FROM dual CONNECT BY LEVEL < 27
  4  /

L        CNT                                                                    
- ----------                                                                    
A          3                                                                    
B          1                                                                    
C          1                                                                    
D          0                                                                    
E          0                                                                    
F          0                                                                    
G          0                                                                    
H          0                                                                    
I          0                                                                    
J          0                                                                    
K          0                                                                    
L          0                                                                    
M          0                                                                    
N          0                                                                    
O          0                                                                    
P          0                                                                    
Q          0                                                                    
R          0                                                                    
S          0                                                                    
T          0                                                                    
U          0                                                                    
V          0                                                                    
W          0                                                                    
X          0                                                                    
Y          0                                                                    
Z          0                                                                    
a          0                                                                    
b          0                                                                    
c          0                                                                    
d          1                                                                    
e          0                                                                    
f          0                                                                    
g          1                                                                    
h          1                                                                    
i          0                                                                    
j          0                                                                    
k          0                                                                    
l          0                                                                    
m          0                                                                    
n          0                                                                    
o          0                                                                    
p          0                                                                    
q          0                                                                    
r          3                                                                    
s          0                                                                    
t          0                                                                    
u          3                                                                    
v          0                                                                    
w          0                                                                    
x          0                                                                    
y          0                                                                    
z          0                                                                    

52 rows selected.

SQL> spool off

obviously if you don't want it case sensitive, just use the first select statement, and UPPER() the text.

Please let me know if this is what you wanted.

Regards

Tharg

Grinding away at things Oracular

 

[Thargy]

smp,

perhaps

SELECT *
  FROM
      (
      SELECT CHR(LEVEL+64) letter,LENGTH('AABCghdururAur') -  LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+64))) cnt  FROM dual CONNECT BY LEVEL < 27
      UNION
      SELECT CHR(LEVEL+96) letter,LENGTH('AABCghdururAur') -  LENGTH(REPLACE('AABCghdururAur',CHR(LEVEL+96))) cnt FROM dual CONNECT BY LEVEL < 27
      )
 WHERE cnt > 0

might be a tad more elegant though, it dumps all the zero results.

Good enough?

Tharg

Grinding away at things Oracular

 

[SmpTraining]

Wow! That's great - thanks guys. I wasn't expecting this to be possible. It opens up all sorts of possible efficiencies in our queries.

Thanks again - A Star to both of you!

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[SantaMufasa]

Steve,

Excellent contributions from Taupirho and Tharg! Just be aware that they each built their code sets based upon your implied request for a 1-character-string matching:

Is there anyway I can count all the As, Bs and Cs

If you wish to count matches of a multi-character string patterns (e.g. "xyz", "hello", et cetera), then their code will work properly, with modifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.