Understanding SQL string functions

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.

ASCII

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle development languages How to check SQL query construction with the Mimer Validator The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table How to create an SQL CHECK constraint for two letters Oracle and SQL Oracle tutorial library: SearchOracle.com's learning guides Can I specify Oracle column order in my database table? Review: Oracle's 11g R2 database has some good and bad SELECT statement syntax and examples Oracle PL/SQL tutorial PL/SQL datatypes in Oracle Stored procedures in PL/SQL PL/SQL functions and triggers in Oracle Do I need a license for SQL Developer Data Modeler in Oracle? Using the SQL GROUP BY clause for counting combinations Oracle and SQL Research Oracle database administrator What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment How to create Datafiles in a Data Guard (10g) environment

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary autonomous transaction  (SearchOracle.com) CFML  (SearchOracle.com) dynamic SQL  (SearchOracle.com) foreign key  (SearchOracle.com) Java Database Connectivity  (SearchOracle.com) Open Database Connectivity  (SearchOracle.com) Oracle  (SearchOracle.com) stored procedure  (SearchOracle.com) The Open Group  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

iry 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 < 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

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:

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.

[IMAGE]
FIGURE 8-1. ASCII function in unique numeric ID formula

BIT_LENGTH

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

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

[IMAGE]
FIGURE 8-2. Account IDs converted to numeric IDs

[IMAGE]
FIGURE 8-3. BIT_LENGTH function

[IMAGE]
FIGURE 8-4. Bit Length report on account numbers

Download the chapter "Using Column Formulas with Text Data" in PDF form.