SUBSTR

The SUBSTR function in Oracle SQL is used to extract a substring from a larger string. It allows you to specify the starting position and the length of the substring to be extracted. This function is widely used in string manipulation tasks such as data extraction, formatting, and trimming.

 

1. Syntax of the SUBSTR Function

The syntax for the SUBSTR function is:

SUBSTR(string, start_position, length)

• string: The input string (or column) from which the substring is to be extracted.
• start_position: The position of the first character to extract. The index is 1-based, meaning the first character of the string is at position 1. If start_position is negative, it counts from the end of the string.
• length (Optional): The number of characters to extract starting from the start_position. If length is omitted, SUBSTR extracts from the start_position to the end of the string.

 

2. Key Points of the SUBSTR Function

1. 1-Based Indexing: The index for start_position is 1-based. The first character of the string is at position 1, not 0.
2. Negative Indexing: If start_position is a negative number, it counts backwards from the end of the string. For example, -1 refers to the last character of the string, -2 to the second-last character, and so on.
3. Length is Optional: If you don't provide a length, SUBSTR extracts from the start_position to the end of the string.
4. Handles NULL: If string or start_position is NULL, SUBSTR returns NULL.
5. Empty Strings: If the string is empty, SUBSTR returns an empty string ('').
6. Truncation: If length exceeds the number of available characters, SUBSTR will only return the available characters.

 

3. Default Behavior of SUBSTR

If no length is provided, SUBSTR extracts the substring from the start_position to the end of the string.

Example:

SELECT SUBSTR('Hello World', 7) FROM dual;

Output: World

Explanation: Starting from position 7, SUBSTR extracts all characters from World until the end of the string.

 

4. Extracting a Substring with Length

You can specify how many characters you want to extract by providing a length parameter.

Example:

SELECT SUBSTR('Hello World', 1, 5) FROM dual;

Output: Hello

Explanation: Starting at position 1, SUBSTR extracts the first 5 characters: Hello.

 

5. Using Negative Indexing with SUBSTR

Negative values for start_position are used to extract a substring starting from the end of the string.

• -1 refers to the last character,
• -2 refers to the second last character,
• And so on.

Example:

SELECT SUBSTR('Hello World', -5) FROM dual;

Output: World

Explanation: Starting from the 5th last character (counting from the end), SUBSTR extracts World.

 

6. Extracting Substrings with Negative Length

You can also use negative values for length. This will allow you to extract a substring that extends from the end of the string.

Example:

SELECT SUBSTR('Hello World', 1, -6) FROM dual;

Output: Hello

Explanation: The SUBSTR starts at position 1 and extracts the string up to 6 characters from the end.

 

7. Behavior with NULL Values

If either the string or start_position is NULL, SUBSTR will return NULL.

Example:

SELECT SUBSTR(NULL, 1, 3) FROM dual;

Output: NULL

Explanation: Since the string is NULL, SUBSTR returns NULL.

 

8. Using SUBSTR on Empty Strings

If the string is empty (''), SUBSTR will return an empty string.

Example:

SELECT SUBSTR('', 1, 3) FROM dual;

Output: '' (Empty string)

Explanation: The string is empty, so SUBSTR returns an empty string.

 

9. Combining SUBSTR with Other Functions

You can combine SUBSTR with other functions such as LENGTH, INSTR, or CONCAT to perform more advanced string manipulations.

Example:

SELECT SUBSTR('Hello World', INSTR('Hello World', 'World')) FROM dual;

Output: World

Explanation: INSTR finds the position of World, and SUBSTR extracts the substring starting from that position.

 

10. Performance Considerations

• Indexes: Using SUBSTR on indexed columns may prevent Oracle from using the index efficiently, potentially slowing down query performance.
• Optimizing String Operations: When working with large datasets or long strings, ensure that SUBSTR is used appropriately to avoid unnecessary computations.

 

11. Example Queries Using SUBSTR

• Example 1: Extracting the first 6 characters from a string:

SELECT SUBSTR('Oracle SQL', 1, 6) FROM dual;

Output: Oracle

Explanation: Extracts the first 6 characters from the string.

• Example 2: Extracting a substring starting from position 7:

SELECT SUBSTR('Hello World', 7, 5) FROM dual;

Output: World

Explanation: Starting from position 7, SUBSTR extracts 5 characters.

• Example 3: Using SUBSTR with negative indexing:

SELECT SUBSTR('Oracle SQL', -3) FROM dual;

Output: SQL

Explanation: Starting from the 3rd character from the end, SUBSTR extracts the remaining characters.

• Example 4: Extracting from a NULL value:

SELECT SUBSTR(NULL, 1, 5) FROM dual;

Output: NULL

Explanation: Since the string is NULL, the result is NULL.

 

12. Common Use Cases for SUBSTR

1.     Extracting Parts of a String:

• Use SUBSTR to extract a specific part of a string, such as extracting a name, date, or code from a larger string.

2.     Data Cleaning:

• Use SUBSTR to trim unwanted characters or parts of a string from the beginning or end.

3.     Parsing Fixed-Format Data:

• SUBSTR is helpful when working with fixed-width formatted data, such as extracting a specific field from a fixed-width text.

4.     String Formatting:

• It is often used in conjunction with CONCAT to format output in a certain way (e.g., separating data or adding delimiters).

 

13. FAQs about the SUBSTR Function

1. Can SUBSTR be used to remove characters from the beginning or end of a string?

• Yes, SUBSTR can remove characters by specifying the correct start_position and length. If you want to remove characters from the beginning, you can start from a later position. If you want to remove characters from the end, you can use negative indexing or a negative length.

2. What happens if the start_position is larger than the length of the string?

• Answer: If the start_position exceeds the length of the string, SUBSTR returns an empty string.

3. Is SUBSTR case-sensitive?

• Answer: Yes, SUBSTR is case-sensitive. It treats uppercase and lowercase letters as different characters.

4. Can SUBSTR return an empty string?

• Answer: Yes, if the start_position is larger than the string length, or if length is 0 or negative, SUBSTR will return an empty string.

5. Can SUBSTR be used with columns in a table?

• Answer: Yes, you can use SUBSTR to extract substrings from columns in a table. For example, extracting the first 5 characters from a column containing email addresses.

 

14. Conclusion

The SUBSTR function in Oracle SQL is a versatile and powerful tool for string manipulation. It enables you to extract portions of strings based on position and length, making it ideal for use cases involving data extraction, trimming, formatting, and more.