CHR

The CHR function in Oracle SQL is used to return the character that corresponds to a given ASCII code. It essentially converts a numeric ASCII value into its corresponding character representation, making it useful for operations like creating dynamic strings, inserting special characters, or manipulating data in specific formats.

 

1. Syntax of the CHR Function

CHR(number)

• number: The numeric ASCII value (ranging from 0 to 255) whose corresponding character is to be returned.

 

2. Key Points about the CHR Function

1. Returns a Single Character: The CHR function returns a single character corresponding to the ASCII value passed as an argument.
2. ASCII Range: The valid range for the ASCII value is from 0 to 255, corresponding to the extended ASCII character set. Values outside this range will result in an error.
3. Non-Printable Characters: The CHR function can also be used to return non-printable ASCII characters (such as control characters like newline or tab).
4. Special Characters: It can be used to return characters like newline (CHR(10)), tab (CHR(9)), carriage return (CHR(13)), and other special characters, which are often used in data formatting.
5. Character Representation: The numeric value you provide is interpreted as an ASCII code, and CHR returns the character corresponding to that ASCII code.

 

3. Example Usage of CHR

Example 1: Return the character corresponding to ASCII code 65

SELECT CHR(65) FROM dual;

Output: A

Explanation: ASCII code 65 corresponds to the letter A.

Example 2: Return the character corresponding to ASCII code 10 (Line Feed)

SELECT CHR(10) FROM dual;

Output: (Line Feed or New Line character)

Explanation: ASCII code 10 corresponds to the newline (Line Feed) character.

Example 3: Return the character corresponding to ASCII code 9 (Tab)

SELECT CHR(9) FROM dual;

Output: (Tab character)

Explanation: ASCII code 9 corresponds to the horizontal tab character.

 

4. Special Control Characters in CHR

The CHR function can be used to return various control characters that are part of the ASCII set. Some commonly used control characters include:

• CHR(9): Horizontal tab (Tab character)
• CHR(10): Line feed (Newline character)
• CHR(13): Carriage return
• CHR(27): Escape character (used for various special formatting operations)
• CHR(32): Space character

 

5. Combining CHR with Other Functions

You can use CHR in combination with other functions like CONCAT to construct strings that include special characters.

Example 1: Using CHR to Insert a Line Break

SELECT 'Hello' || CHR(10) || 'World' FROM dual;

Output:

Hello

World

Explanation: The CHR(10) inserts a line break (newline) between Hello and World.

Example 2: Using CHR for Formatting Output

SELECT 'ID' || CHR(9) || 'Name' || CHR(9) || 'Age' FROM dual;

Output: ID Name Age

Explanation: CHR(9) inserts tab spaces between the columns, which can be useful for generating formatted text output.

 

6. Using CHR for Encoding and Data Conversion

You can use CHR to insert special characters into data or perform operations like creating dynamic strings with non-printable characters. It is useful in data formatting, data cleaning, and reports generation.

Example 1: Inserting a Carriage Return into a String

SELECT 'Hello' || CHR(13) || 'World' FROM dual;

Output:

Hello

World

Explanation: The CHR(13) inserts a carriage return, moving the cursor to the beginning of the next line.

 

7. Behavior with NULL Values

If the number passed to CHR is NULL, the result will also be NULL.

Example:

SELECT CHR(NULL) FROM dual;

Output: NULL

Explanation: Since the input is NULL, the result is also NULL.

 

8. Performance Considerations

• Efficient String Handling: When using CHR to insert special characters into large strings, make sure you are not generating excessive unnecessary control characters, which could negatively impact the performance and readability of the data.
• Character Set: Ensure that the ASCII codes you are working with are consistent with the character set of your Oracle database. While the standard ASCII table has values from 0 to 255, databases may support extended character sets, and certain characters may not behave as expected in different character encodings.

 

9. Example Queries Using CHR

• Example 1: Generating a string with special characters:

SELECT 'Line 1' || CHR(10) || 'Line 2' || CHR(10) || 'Line 3' FROM dual;

Output:

Line 1

Line 2

Line 3

Explanation: CHR(10) is used to insert line breaks between the lines.

• Example 2: Generating a string with tab spaces between words:

SELECT 'Item' || CHR(9) || 'Price' || CHR(9) || 'Quantity' FROM dual;

Output: Item Price Quantity

Explanation: CHR(9) is used to insert tab spaces.

• Example 3: Inserting carriage returns to format data output:

SELECT 'Hello' || CHR(13) || 'World' FROM dual;

Output:

Hello

World

Explanation: CHR(13) inserts a carriage return, so World appears on the next line.

 

10. Common Use Cases for CHR

1.     Inserting Control Characters: CHR is commonly used to insert non-printable characters like newline (CHR(10)), tab (CHR(9)), or carriage return (CHR(13)) into strings for formatting purposes.

2.     Generating Dynamic Strings: When building strings dynamically with embedded control characters, CHR provides a method for inserting characters that cannot be typed directly.

3.     Data Formatting: In reports or file generation (e.g., generating CSV or tab-delimited files), CHR is useful for inserting the appropriate separators or line breaks.

4.     String Manipulation: CHR can be used in string manipulation tasks to manage special characters that are part of the string.

 

11. FAQs about the CHR Function

1. Can CHR handle values greater than 255?

• No, the CHR function in Oracle SQL can only handle values between 0 and 255. For values beyond this range, an error will occur.

2. Can CHR be used to insert non-printable characters?

• Yes, CHR can be used to insert control characters such as newline (CHR(10)), tab (CHR(9)), and others. This is useful for formatting text data.

3. What is the result of CHR(0)?

• CHR(0) returns the null character. This is a non-printable character with no visual representation, but it exists in the extended ASCII set.

4. Can I use CHR with any character encoding?

• CHR is based on ASCII values, and its behavior is influenced by the character set used in your Oracle database. For extended characters or Unicode characters, you would need to use other functions like NCHR (for Unicode) or handle it through specific character set configurations.