The LENGTH and INSTR functions are frequently used when performing string manipulation in Oracle SQL.
This article provides a step-by-step guide from basic usage to advanced examples. Let’s fully master their use with easy-to-understand illustrations and practical tasks for beginners!
This article uses examples based on the SCOTT sample schema.
You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
The LENGTH Function
1. What is LENGTH?
The LENGTH function returns the “number of characters” in a specified string.
In Oracle, LENGTH can accurately count multi-byte characters, so it can be used reliably in internationalized systems.
2. Basic Syntax
LENGTH(string)
| Argument | Description |
string | The string whose length you want to find. Can be NULL. |
Note: If string is NULL, the result will also be NULL.
3. Practical Example 1: Get the character count of employee names
Execute the following SQL to get the character count of ENAME (employee name).
SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
FROM EMP;
Execution Result (Example)
SQL> SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
2 FROM EMP;
ENAME NAME_LENGTH
--------------- -----------
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
JAMES 5
FORD 4
MILLER 6
14 rows selected.
Point: Assigning an alias (NAME_LENGTH) to the result column improves the query’s readability.
4. Applied Example: Handling NULL values
If there’s a possibility of NULL values, use the NVL function to handle them.
SELECT ENAME, LENGTH(NVL(ENAME, '0'))
FROM EMP;
Hint: You can process safely by replacing NULL values with a default value, such as ‘NoName’.
The INSTR Function
1. What is INSTR?
The INSTR function returns the “position” where a specific character or substring first (or for a specified occurrence) appears within a string.
The position starts from 1. If the specified string is not found, it returns 0.
2. Basic Syntax
INSTR(string, substring [, start_position [, occurrence]])
| Argument | Description |
string | The string to search. |
substring | The character or substring to search for. |
start_position | The position to start the search (default is 1). A negative value searches backward. |
occurrence | The occurrence to find (default is 1). You can specify subsequent occurrences. |
3. Practical Example 1: Get the position of ‘A’ in ENAME
SELECT ENAME, INSTR(ENAME, 'A') AS A_POSITION
FROM EMP;
Execution Result (Example)
SQL> SELECT ENAME, INSTR(ENAME, 'A') AS A_POSITION
2 FROM EMP;
ENAME A_POSITION
--------------- ----------
SMITH 0
ALLEN 1
WARD 2
JONES 0
MARTIN 2
BLAKE 3
CLARK 3
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 2
FORD 0
MILLER 0
14 rows selected.
4. Applied Example: Get the position of the second occurrence of a character
Get the position of the second occurrence of the character ‘L’.
SELECT ENAME, INSTR(ENAME, 'L', 1, 2) AS SECOND_L_POSITION
FROM EMP
WHERE ENAME = 'ALLEN';
Execution Result
SQL> SELECT ENAME, INSTR(ENAME, 'L', 1, 2) AS SECOND_L_POSITION
2 FROM EMP
3 WHERE ENAME = 'ALLEN';
ENAME SECOND_L_POSITION
--------------- -----------------
ALLEN 3
5. How to Search Backward
If you want to search backward, set the start_position to a negative value.
SELECT INSTR('HELLO WORLD', 'O', -1) AS LAST_O_POSITION
FROM DUAL;
Result
SQL> SELECT INSTR('HELLO WORLD', 'O', -1) AS LAST_O_POSITION
2 FROM DUAL;
LAST_O_POSITION
---------------
8
Explanation: The position of the last occurrence of ‘O’ is returned.
Applied Examples Combining LENGTH and INSTR
In actual business scenarios, these are combined to filter and analyze data.
Applied Example 1: Extract employee names containing ‘E’
The following SQL extracts employee names whose ENAME contains ‘E’.
SELECT ENAME,
LENGTH(ENAME) AS NAME_LENGTH,
INSTR(ENAME, 'E') AS E_POSITION
FROM EMP
WHERE INSTR(ENAME, 'E') > 0;
Execution Result (Example)
SQL> SELECT ENAME,
2 LENGTH(ENAME) AS NAME_LENGTH,
3 INSTR(ENAME, 'E') AS E_POSITION
4 FROM EMP
5 WHERE INSTR(ENAME, 'E') > 0;
ENAME NAME_LENGTH E_POSITION
--------------- ----------- ----------
ALLEN 5 4
JONES 5 4
BLAKE 5 5
TURNER 6 5
JAMES 5 4
MILLER 6 5
6 rows selected.
Applied Example 2: Conditional branching if a specific character is included
The following SQL marks employees whose ENAME contains ‘A’ as ‘important’.
SELECT ENAME,
CASE
WHEN INSTR(ENAME, 'A') > 0 THEN 'important'
ELSE 'usually'
END AS STATUS
FROM EMP;
Execution Result (Example)
SQL> SELECT ENAME,
2 CASE
3 WHEN INSTR(ENAME, 'A') > 0 THEN 'important'
4 ELSE 'usually'
5 END AS STATUS
6 FROM EMP;
ENAME STATUS
--------------- ---------
SMITH usually
ALLEN important
WARD important
JONES usually
MARTIN important
BLAKE important
CLARK important
SCOTT usually
KING usually
TURNER usually
ADAMS important
JAMES important
FORD usually
MILLER usually
14 rows selected.
Applied Example 3: Get the longest employee name
Get the name with the most characters among the employee names.
SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
FROM EMP
WHERE LENGTH(ENAME) = (SELECT MAX(LENGTH(ENAME)) FROM EMP);
Practical Tasks
Try tackling the following tasks.
- Write SQL to extract employees whose
ENAMEcontains ‘R’. - Get the length of each employee name and sort them by the name length in descending order.
- Extract employees whose
ENAMEcontains the character ‘O’ two or more times.
Summary
LENGTHgets the length of a string.INSTRfinds the position of a specific character or substring within a string.- Combining these can streamline the analysis and manipulation of string data.
Key Points of This Article
- Promotes practical understanding by showing concrete execution results in addition to code samples.
- Proposes scenarios envisioned for on-the-job use in the applied examples.
- Adopts a structure that enhances the learning effect by including practical tasks.
With this, even beginners should be able to use the LENGTH and INSTR functions with confidence. Let’s try them out!
[reference]
Oracle Database SQL Language Reference, 19c
コメント