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;
Posts
14 comments:
Thanks, this solution rocks!
in one word:
AMAZING!!!!!!!!!!!!
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
regexp would be easier for this and more complex string transformations
SELECT LENGTH(REGEXP_REPLACE('@5@-chars in this@@text@','[^@]')) FROM DUAL;
Very clever! Thanks!
Uhm
select count(*) where
yourcolumn like '%@%@%'
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.
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.
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.
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.
Updated to address Abhijit's observation.
SELECT LENGTH(REGEXP_REPLACE('aa/bbbb/cc/dd','[^/]')) FROM DUAL;
This is awesome...Thanks jan leers
If you are going with regex, why not use regexp_count?
select regexp_count('this@here@there.com', '[@]') from dual;
When I originally wrote this, I didn't have any access to RegEx.
Post a Comment