Making Use of Translate - Revision history

WikiSysop at 18:43, 15 June 2016

2016-06-15T18:43:23Z

WikiSysop at 18:39, 15 June 2016

2016-06-15T18:39:59Z

WikiSysop at 18:16, 15 June 2016

2016-06-15T18:16:05Z

WikiSysop at 18:01, 15 June 2016

2016-06-15T18:01:09Z

WikiSysop at 08:56, 15 June 2016

2016-06-15T08:56:04Z

WikiSysop at 08:41, 15 June 2016

2016-06-15T08:41:46Z

WikiSysop at 08:12, 15 June 2016

2016-06-15T08:12:25Z

WikiSysop at 08:11, 15 June 2016

2016-06-15T08:11:32Z

WikiSysop at 08:10, 15 June 2016

2016-06-15T08:10:04Z

WikiSysop: 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..."

2016-06-14T23:03:45Z

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..."

New page

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;

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

This would yield something like:

<pre>
PHONE_FORMAT ROW_COUNT
(999) 999 9999 2567
999-999-9999. 23848
(99999) 999999. 73658
+99 999 999 999. 3

</pre>

← Older revision		Revision as of 18:43, 15 June 2016
Line 51:		Line 51:
	This will give all the rows with non alphabetic characters and show them. Depending on the requirements, other characters may be excluded too - such as space or hyphen.		This will give all the rows with non alphabetic characters and show them. Depending on the requirements, other characters may be excluded too - such as space or hyphen.

−	I hope you found this tip useful. For more tips, ~~take a look at our~~ eBook:	+	I hope you found this tip useful. For more hints and tips like this, see the eBook:
		+
		+	{\| class="wikitable"
		+	\|-
		+	\|[[File:UsingFunctionsInPeopleSoftQuerySmall.jpg\|90px\|link=http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21]]
		+
		+	\|
		+	[http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21 Using Functions in PeopleSoft Query]
		+
		+	[[PeopleSoft Query]]
		+
		+	\|}

@@ Line 1: / Line 1: @@
 The [[Oracle Database]] has a SQL function called "translate" which can do character by character switching and removal. The format of the function is:
-translate(''original_text, source_characters, target_characters'')
+  translate(''original_text, source_characters, target_characters'')
 If any of the ''source_characters'' are found in the ''original_text'' then they are replaced with the corresponding ''target_character''. So any occurrences of the first source character in the ''original_text'' will be replaced with the first character of the target, the second with the second and so on.
@@ Line 14: / Line 14: @@
 <pre>
-select translate(PHONE_NBR,1234567890','9999999999') PHONE_FORMAT, count(*) ROW_COUNT
+select translate(PHONE_NBR
 From PERSONAL_PHONE
 group by translate(PHONE_NBR,'1234567890','9999999999')
@@ Line 40: / Line 43: @@
 <pre>
 Select
-translate(DESCRIPTION, '~abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ','~') ODD_CHAR
+translate(DESCRIPTION
 From PRODUCTS
 </pre>

@@ Line 3: / Line 3: @@
 translate(''original_text, source_characters, target_characters'')
 ==Finding Formats==
@@ Line 34: / Line 36: @@
 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. You just need to list those characters you think are OK. If there is no corresponding character in the second string then it gets removed. If the replacement characters is empty however, translate gets over ambitious and removes all the characters, so we have to include one odd character to map to itself at the beginning. We can pick any character that isn't in our "normal" list. It does not need to exist in the column we are examining.
-So let's say our "normal" list is alphanumeric characters, and we have chosen open square brackets as our oddity.
+So let's say our "normal" list is alphabetic characters, and we have chosen tilde (~) as our oddity. This would give us the following translate:

← Older revision		Revision as of 18:01, 15 June 2016
Line 1:		Line 1:
		+	The [[Oracle Database]] has a SQL function called "translate" which can do character by character switching and removal. The format of the function is:
		+
		+	translate(''original_text, source_characters, target_characters'')
		+
		+
		+
	==Finding Formats==		==Finding Formats==

← Older revision		Revision as of 08:56, 15 June 2016
Line 26:		Line 26:
	==Finding Odd Characters==		==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.	+	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. You just need to list those characters you think are OK. If there is no corresponding character in the second string then it gets removed. If the replacement characters is empty however, translate gets over ambitious and removes all the characters, so we have to include one odd character to map to itself at the beginning. We can pick any character that isn't in our "normal" list. It does not need to exist in the column we are examining.
		+
		+	So let's say our "normal" list is alphanumeric characters, and we have chosen open square brackets as our oddity.

@@ Line 1: / Line 1: @@
 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;
+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>
@@ Line 20: / Line 22: @@
 The last three might have been difficult to find without this trick.

@@ Line 15: / Line 15: @@
 (999) 999 9999   2567
 -999-9999     23848
-(99999) 999999     76658
+(99999) 999999   76658
-+99 999 999 999 3
++99 999 999 999  3
 </pre>
 The last three might have been difficult to find without this trick.

@@ Line 12: / Line 12: @@
 <pre>
-PHONE_FORMAT   ROW_COUNT
+PHONE_FORMAT     ROW_COUNT
 (999) 999 9999   2567
--999-9999.    23848
+-999-9999     23848
-(99999) 999999.   76658
+(99999) 999999     76658
-+99 999 999 999. 3
++99 999 999 999 3
 </pre>
 The last three might have been difficult to find without this trick.