Oracle SUBSTR function
Description
The SUBSTR functions returns the specified number (substring_length) of characters from a particular position of a given string. It has several variants, including SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4, each tailored to handle specific data types and encoding requirements. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.
Uses of Oracle SUBSTR Function
- Extracting Substrings: Retrieve a portion of a string starting from a specified position.
- Handling Multibyte Characters: Use SUBSTRB to deal with multibyte character sets.
- Working with Unicode: Utilize SUBSTRC for complete Unicode characters.
- Manipulating Encoded Data: Employ SUBSTR2 and SUBSTR4 for UCS2 and UCS4 encoded data.
- Negative Positioning: Extract parts of a string starting from the end.
- Domain Extraction: Isolate domain names from URLs.
- Name Parsing: Extract specific parts of names, such as last names, from full names.
Different Variants of SUBSTR:
- SUBSTRB: Useful in environments dealing with multibyte character sets, particularly in scenarios where byte manipulation is critical.
- SUBSTRC: Focuses on complete Unicode characters, which is essential when dealing with internationalization.
- SUBSTR2 and SUBSTR4: Explain their specific use cases in dealing with UCS2 and UCS4 encoded data, respectively.
Syntax:
{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])
Parameters:
| Name | Description | Data Types |
|---|---|---|
| char | A string from which a substring is to be returned. | CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not allow char to be a CLOB or NCLOB. |
| position | An integer indicating a string position within the string char. | NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. |
| substring_length | An integer indicating a number of characters to be returned. | NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. |
Behavior when position:
- If the position is 0, then it is treated as 1.
- If the position is positive, then Oracle counts from the beginning of char to find the first character.
- If the position is negative, then Oracle counts backward from the end of char.
- If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.
Return Value Type
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not allow char to be a CLOB or NCLOB
Applies to
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Visual Presentation
Examples: Oracle SUBSTR function
The following example returns several specified substrings of "w3resource":
SELECT SUBSTR('w3resource',3,4) "Substring"
FROM DUAL;
Sample Output:
Substring --------- reso
Handling negative start position to extract from the end:
SELECT SUBSTR('w3resource',-5,4) "Substring"
FROM DUAL;
Sample Output:
Substring --------- ourc
Extracting the domain name from a URL:
SELECT SUBSTR('http://www.example.com/page', INSTR('http://www.example.com/page', '//') + 2) AS "Domain"
FROM DUAL;
Extracting last name from a full name string:
SELECT SUBSTR('John Doe', INSTR('John Doe', ' ') + 1) AS "Last Name"
FROM DUAL;
Related Functions:
- INSTR: Locates the position of a substring within a string.
- LENGTH: Returns the length of a string.
- REPLACE: Replaces occurrences of a substring within a string.
Common Errors and Troubleshooting:
- Invalid Position: Ensure the position parameter is within the bounds of the string. A position of 0 is treated as 1.
- Substring Length Exceeds String: If the `substring_length` exceeds the remaining length of the string, Oracle returns characters up to the end of the string.
- Handling NULL Values: If the input string is NULL, `SUBSTR` will return NULL. Ensure proper NULL handling in your queries.
Frequently Asked Questions (FAQ) - Oracle SUBSTR Function
1. What does the Oracle SUBSTR function do?
The Oracle SUBSTR function extracts a specified number of characters (substring_length) from a given string (char), starting at a particular position (position). It supports various forms to handle different encoding and byte scenarios.
2. What are the different variants of the Oracle SUBSTR function and their use cases?
- SUBSTR: Extracts characters based on character positions.
- SUBSTRB: Extracts characters based on byte positions, crucial for multibyte character sets.
- SUBSTRC: Handles Unicode complete characters, ideal for internationalization.
- SUBSTR2: Works with UCS2 code points, suitable for specific character encoding needs.
- SUBSTR4: Operates on UCS4 code points, useful for dealing with extended character sets.
3. How does the function handle the position parameter?
- Position 0: Treated as 1, starting extraction from the first character.
- Positive Position: Counts from the beginning of the string.
- Negative Position: Counts backward from the end of the string.
4. What happens if the substring_length is omitted or less than 1?
- Omitted Substring Length: The function returns all characters from the start position to the end of the string.
- Substring Length Less Than 1: The function returns NULL.
5. What data types can be used with the char parameter in the Oracle SUBSTR function?
The char parameter can be of types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. However, for SUBSTRC, SUBSTR2, and SUBSTR4, the char parameter cannot be a CLOB or NCLOB.
6. What return value types can be expected from the Oracle SUBSTR function?
The return types include CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are SUBSTRC, SUBSTR2, and SUBSTR4, which do not support CLOB or NCLOB return types.
7. What Oracle versions support the Oracle SUBSTR function?
The SUBSTR function is available in Oracle Database versions including Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, and Oracle 8i.
8. What are common errors or pitfalls when using the Oracle SUBSTR function?
- Invalid Position: Ensure the position is within the string's bounds. A position of 0 is treated as 1.
- Substring Length Exceeds String: If the substring length is too long, Oracle returns characters up to the end of the string.
- Handling NULL Values: If the input string is NULL, the function returns NULL. Proper handling of NULL values is essential to avoid unexpected results.