Monday, March 30, 2009

Count occurences of a character in a string (Oracle)

Recently, I needed to see how many times the "@" [at sign] was in a particular column more than once.

So Abhijit noticed an interesting detail, if you are counting characters where the input string is nothing but the same repeating search character, then the resulting count is incorrect. I concatenated in a "random" character to prevent this scenario. This only affects the "pure Oracle solution".

Pure Oracle Solution (Updated)

select
   :str as "string",
   :chr as "character",
   length(:str||chr(1)) - length(translate(:str||chr(1),chr(0)||:chr,chr(0))) as "count"
from dual;
select *
from 
(
   select
      ia.internet_address_id as "InternetAddressID",
      ia.internet_address as "string",
      '@' as "character",
      length(ia.internet_address||chr(1)) - length(translate(ia.internet_address||chr(1),chr(0)||'@',chr(0))) as "occurences"
   from internet_address ia
)
where "occurences" > 1;
Regular Expression Based Solution (Thanks Jan Leers)
SELECT *
FROM
(
 SELECT 
  ia.internet_address_id as "InternetAddressID",
      ia.internet_address as "InternetAddress",
  LENGTH(REGEXP_REPLACE(ia.internet_address,'[^@]')) as "Occurences"
 FROM 
  internet_address ia
)
WHERE "Occurences" > 1;

14 comments:

edsox5 said...

Thanks, this solution rocks!

Anonymous said...

in one word:
AMAZING!!!!!!!!!!!!

malik_aryan said...

YOU CAN ALSO USE THIS QUERY
SELECT LENGHT('STRING')-LENGTH(REPLACE('STRING','WHICH CHARACTER U WANT TO COUNT')) FROM DUAL

IF U R CONFUSED THEN MAIL ME

jan leers said...

regexp would be easier for this and more complex string transformations

SELECT LENGTH(REGEXP_REPLACE('@5@-chars in this@@text@','[^@]')) FROM DUAL;

Mark D said...

Very clever! Thanks!

Anonymous said...

Uhm

select count(*) where
yourcolumn like '%@%@%'

Jason Vogel said...

Uhm... you're missing the point. This is about a technique, not just a single "test". You could use this test for any count > 1.

sap upgrade transactions said...

Wow. Thank you so much for this excellent solution. I just tested this code and is completely satisfied with it. Could you please also provide me a possible solution if I wanted to count the occurrence of a string rather than a character in a text file which will be passed at runtime by the user.

Jason Vogel said...

I don't have a PL/SQL solution for counting characters or strings from a file. You'll need it loaded to table for either of the solutions work.

abhijit said...

There's one issue with this code. If you attempt to count a field with nothing but the same character repeated over and over again, you'll not get a correct count.

Jason Vogel said...

Updated to address Abhijit's observation.

Anonymous said...

SELECT LENGTH(REGEXP_REPLACE('aa/bbbb/cc/dd','[^/]')) FROM DUAL;

This is awesome...Thanks jan leers

Unknown said...

If you are going with regex, why not use regexp_count?

select regexp_count('this@here@there.com', '[@]') from dual;

Jason Vogel said...

When I originally wrote this, I didn't have any access to RegEx.