Fun with Oracle Strings

Today I needed to find a way to count the number of unique email domains in a table. I figured there was a way of getting the index of a string in another string and sure enough there is. This did the trick in Oracle:

select count(1), SUBSTR(email, INSTR(email, '@', 1, 1)+1) from SOMETABLE group by SUBSTR(email, INSTR(email, '@', 1, 1)+1) order by count(1) desc

The INSTR function gives you the location in a string where another string is located. See the following link for more on the INSTR function: http://www.techonthenet.com/oracle/functions/instr.php

I’ve always found the way Oracle handles case interesting. It looks like they are changing things a little starting with 10G: http://blogs.ittoolbox.com/database/solutions/archives/005951.asp

del.icio.us:Fun with Oracle Strings digg:Fun with Oracle Strings spurl:Fun with Oracle Strings wists:Fun with Oracle Strings simpy:Fun with Oracle Strings newsvine:Fun with Oracle Strings blinklist:Fun with Oracle Strings furl:Fun with Oracle Strings reddit:Fun with Oracle Strings fark:Fun with Oracle Strings blogmarks:Fun with Oracle Strings Y!:Fun with Oracle Strings smarking:Fun with Oracle Strings magnolia:Fun with Oracle Strings segnalo:Fun with Oracle Strings gifttagging:Fun with Oracle Strings

This entry was posted on Monday, February 13th, 2006 at 10:14 pm by carsonm and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Fun with Oracle Strings”

  1. darrend Says:
    February 14th, 2006 at 1:02 pm

    So how can you do this with other databases?

  2. steveny Says:
    February 17th, 2006 at 8:19 am

    Here is some SQLProcessor code running on top of mysql that does something similar

    
    SQLProcessor sqlProcessor = new SQLProcessor("Get spam count",
                                                 "SELECT substring(email, instr(email, '@')+1), count(spam_filter) FROM postfix_users " +
                                                 "WHERE spam_filter='Y' " +
                                                 "GROUP BY substring(email, instr(email, '@')+1);")

Leave a Reply