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:

I've always found the way Oracle handles case interesting. It looks like they are changing things a little starting with 10G:


Leave a Reply

Your email address will not be published. Required fields are marked *