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:

[code lang=”sql”]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[/code]

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

[tags]oracle[/tags]