Making Use of Translate

Ever had the problem where some of the rows on a large Oracle Database table do not conform to a particular format? Or you wonder which different formats are in use?

This where the translate command comes into its own. For instance, imagine you have a column the contains telephone numbers, but they are in a variety of formats, and you want to know how many different formats there are and how many rows in each format;

select translate(PHONE_NBR,1234567890','9999999999') PHONE_FORMAT, count(*) ROW_COUNT
From PERSONAL_PHONE
group by translate(PHONE_NBR,'1234567890','9999999999')

This would yield something like:

PHONE_FORMAT     ROW_COUNT
(999) 999 9999   2567
999-999-9999     23848
(99999) 999999     76658
+99 999 999 999 3

The last three might have been difficult to find without this trick.