Difference between revisions of "Making Use of Translate"
From TekiWiki
| Line 1: | Line 1: | ||
| + | ==Finding Formats== | ||
| + | |||
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? | 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? | ||
| − | + | Whether you are using [[SQL Plus]] or [[PeopleSoft Query]], 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; | |
<pre> | <pre> | ||
| Line 20: | Line 22: | ||
The last three might have been difficult to find without this trick. | The last three might have been difficult to find without this trick. | ||
| + | |||
| + | |||
| + | ==Finding Odd Characters== | ||
| + | |||
| + | An odd character is just simply one you weren't expecting. If you get rid of all the "normal" characters all you are left with is oddities. | ||
Revision as of 09:41, 15 June 2016
Finding Formats
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?
Whether you are using SQL Plus or PeopleSoft Query, 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.
Finding Odd Characters
An odd character is just simply one you weren't expecting. If you get rid of all the "normal" characters all you are left with is oddities.
