Difference between revisions of "Making Use of Translate"
From TekiWiki
(Created page with "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...") |
|||
| Line 15: | Line 15: | ||
(999) 999 9999 2567 | (999) 999 9999 2567 | ||
999-999-9999. 23848 | 999-999-9999. 23848 | ||
| − | (99999) 999999. | + | (99999) 999999. 76658 |
+99 999 999 999. 3 | +99 999 999 999. 3 | ||
| − | |||
</pre> | </pre> | ||
| + | |||
| + | The last three might have been difficult to find without this trick. | ||
Revision as of 09:10, 15 June 2016
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.
