 | |||
|
|
||
| |||
| |||
| |
|
Home > Understanding SQL string functions |
| |
|
|
|
|
| Chapter Download: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Oracle CRM On Demand Reporting 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.
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.
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.
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.
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.
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).
Download the chapter "Using Column Formulas with Text Data" in PDF form.
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
