Understanding SQL string functions

Each week, SearchOracle.com will bring you tips and examples of one SQL string function, such as CHAR, CONCAT and REPLACE, in this chapter excerpt from the book Oracle CRM On Demand Reporting.

CRM On Demand Reporting book cover
 

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

More on this book
This chapter is excerpted from the book, Oracle CRM On Demand Reporting, authored by Michael D. Lairson, published by McGraw-Hill, September, 2008. ISBN 0071593047.

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

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

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

 

bit length for account IDs converted to numeric IDs
FIGURE 8-2. Account IDs converted to numeric IDs

BIT_LENGTH function image
FIGURE 8-3. BIT_LENGTH function

 

Bit
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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close