Fun with Oracle Strings

By carson | Published: February 14, 2006

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

Tags: oracle