 |
|
 |
|
|
|
Oracle CRM On Demand Reporting
Chapter 8: Using Column Formulas with Text Data
This chapter of Oracle CRM On Demand Reporting focuses on SQL string functions, which are functions that manipulate textual data such as names of customers, types of opportunities or subjects of activities. Each week, SearchOracle.com will bring you an excerpt from the chapter that describes one string function, including examples and tips for their use.
ASCII: Learn how the ASCII function converts a single character to its corresponding ASCII code. Also, learn the CHAR functions for the extended ASCII characters with the standard ASCII table.
| This week's featured string function: |
BIT_LENGTH: Learn how to use this function to determine the length of a character string in bits to evaluate string size for compliance with other systems or as part of a larger formula.
| Coming soon: |
CHAR: Learn how to use the CHAR function to insert any character into your report with these ASCII code. and extended ASCII code tables.
CHAR_LENGTH or CHARACTER_LENGTH: Find out how the CHAR_LENGTH function returns the length, in number of characters, of a string expression.
The ASCII function converts a single character to its corresponding ASCII code. Table 8-1 shows the standard ASCII table. Review the CHAR function for the extended ASCII characters. If you attempt to use the ASCII function on a string with more than one character, the function returns the ASCII code for only the first character in the string.
| CHAR | ASCII | CHAR | ASCII | CHAR | ASCII |
|---|---|---|---|---|---|
| Null | 0 | + | 43 | V | 86 |
| Start of Heading | 1 | ' | 44 | W | 87 |
| Start of Text | 2 | - | 45 | X | 88 |
| End of Text | 3 | . | 46 | Y | 89 |
| End of Transmission | 4 | / | 47 | Z | 90 |
| Enquiry | 5 | 0 | 48 | [ | 91 |
| Acknowledge | 6 | 1 | 49 | / | 92 |
| Bell | 7 | 2 | 50 | ] | 93 |
| Backspace | 8 | 3 | 51 | ^ | 94 |
| Tab | 9 | 4 | 52 | _ | 95 |
| Line Feed | 10 | 5 | 53 | ` | 96 |
| Vertical Tab | 11 | 6 | 54 | a | 97 |
| Form Feed | 12 | 7 | 55 | b | 98 |
| Carriage Return | 13 | 8 | 56 | c | 99 |
| Shift Out | 14 | 9 | 57 | d | 100 |
| Shift In | 15 | : | 58 | e | 101 |
| Data Link Escape | 16 | ; | 59 | f | 102 |
| Device Control 1 | 17 | <</td> | 60 | g | 103 |
| Device Control 2 | 18 | = | 61 | h | 104 |
| Device Control 3 | 19 | > | 62 | i | 105 |
| Device Control 4 | 20 | ? | 63 | j | 106 |
| Negative Acknowledge | 21 | @ | 64 | k | 107 |
| Synchronous Idle | 22 | A | 65 | l | 108 |
| End Transmission Block | 23 | B | 66 | m | 109 |
| Cancel | 24 | C | 67 | n | 110 |
| End of Medium | 25 | D | 68 | o | 111 |
| Substitute (EOF) | 26 | E | 69 | p | 112 |
| Escape | 27 | F | 70 | q | 113 |
| File Separator | 28 | G | 71 | r | 114 |
| Group Separator | 29 | H | 72 | s | 115 |
| Record Separator | 30 | I | 73 | t | 116 |
| Unit Separator | 31 | J | 74 | u | 117 |
| Space | 32 | K | 75 | v | 118 |
| ! | 33 | L | 76 | w | 119 |
| " | 34 | M | 77 | x | 120 |
| # | 35 | N | 78 | y | 121 |
| $ | 36 | O | 79 | z | 122 |
| % | 37 | P | 80 | { | 123 |
| & | 38 | Q | 81 | | | 124 |
| ' | 39 | R | 82 | } | 125 |
| ( | 40 | S | 83 | ~ | 126 |
| ( | 40 | S | 83 | ~ | 126 |
| ) | 41 | T | 84 | ||
| * | 42 | U | 85 |
TABLE 8-1. Standard ASCII Table
Use of the ASCII function in a report is unusual, and I can think of no reason to use the function on its own. If it is used at all, it would be as part of a larger formula. I imagine there could be a use for this function if you needed to convert a unique text string into a unique numeric value, perhaps for the purposes of importing into another system that required a numeric ID.
|
||||
|
|
|||
|
||||
Function Syntax
ASCII(char-exp) ASCII('O') ASCII(Account."Account ID")
I can think of only a few times I have used the ASCII function in a report, but I do have one very compelling use for the ASCII function. If you find a need to move data from one system to another, Answers On Demand permits you to develop a table and export that data to a file. If you should need to import your data into a system that requires a unique numeric identifier, the ID column in CRM On Demand, being an alphanumeric column, would not comply with this requirement.
You can use the ASCII function to convert the characters in your unique ID to their ASCII numeric equivalent without losing the uniqueness of the ID. Since the ASCII function works on one character at a time, you need to build a formula that converts one character at a time. The following formula actually uses several functions you will find described in this chapter, but the ASCII function is the central component of the formula:
RIGHT(REPLACE (CAST(ASCII(SUBSTRING(Account."Account ID" FROM 3 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 4 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 5 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 6 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 7 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 8 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 9 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 10 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 11 FOR 1)) AS CHAR)|| CAST(ASCII(SUBSTRING(Account."Account ID" FROM 12 FOR 1)) AS CHAR) ,' ',''), 16)
This chapter provides detailed explanations of the RIGHT, REPLACE, and SUBSTRING functions, and the next chapter explains the CAST function. Figures 8-1 and 8-2 show a similar application of this formula column and the result of the conversion from an alphanumeric unique record identifier to a unique numeric record identifier. In the example, I am taking the last 16 digits of the result to create a 16-digit unique numerical identifier.
FIGURE 8-1. ASCII function in unique numeric ID formula
There are 8 bits in a byte, and each Unicode character is 2 bytes in length, or 16 bits. If you need to determine the length of a character string in bits to evaluate string size for compliance with other systems or as part of a larger formula that uses bit length as a condition, then this function can be very helpful.
This function is related to the CHAR_LENGTH and OCTET_LENGTH functions, which are described a little later in the chapter.
Function Syntax
BIT_LENGTH(char_exp) BIT_LENGTH(Account."Account Name") BIT_LENGTH(Account."Account Name" || Account."Location")
In Figure 8-3, I am using the BIT_LENGTH function on a custom field for account numbers. Account numbers should be 12 digits (or 192 bits), but I am having problems with people entering spaces and dashes into the database. Using the BIT_LENGTH function and a simple filter, I can identify those accounts that have a bit length different from 192 (see Figure 8-4).
FIGURE 8-2. Account IDs converted to numeric IDs
FIGURE 8-3. BIT_LENGTH function
FIGURE 8-4. Bit Length report on account numbers
Download the chapter "Using Column Formulas with Text Data" in PDF form.
This was first published in January 2009
Join the conversationComment
Share
Comments
Results
Contribute to the conversation