String formatting is one of the most fundamental aspects of database operations. This article provides a thorough explanation, from basics to applications, of the UPPER, LOWER, and INITCAP functions used for string manipulation in Oracle Database. Let’s master these functions through the following content!
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
1. Basic Explanation of Each Function
1-1. UPPER Function
UPPER converts an entire string to uppercase. It is used, for example, when standardizing search conditions to uppercase or in reports that require an uppercase display.
Syntax
UPPER(string)
Example The following SQL converts the ename column to uppercase for display.
SELECT UPPER(ename) AS upper_case_name
FROM scott.emp;
1-2. LOWER Function
LOWER converts an entire string to lowercase. It is effective for maintaining string consistency, such as in the normalization of email addresses.
Syntax
LOWER(string)
Example
SELECT UPPER(ename), LOWER(ename) AS lower_case_name
FROM scott.emp;
Result
SQL> SELECT UPPER(ename), LOWER(ename) AS lower_case_name
2 FROM scott.emp;
UPPER(ENAM LOWER_CASE
---------- ----------
SMITH smith
ALLEN allen
WARD ward
JONES jones
MARTIN martin
BLAKE blake
CLARK clark
SCOTT scott
KING king
TURNER turner
ADAMS adams
JAMES james
FORD ford
MILLER miller
14 rows selected.
1-3. INITCAP Function
INITCAP converts the first letter of each word to uppercase and all other letters to lowercase. It is ideal for formatting names and titles.
Syntax
INITCAP(string)
Example
SELECT ename, INITCAP(ename) AS initcap_name
FROM scott.emp;
Result
SQL> SELECT ename, INITCAP(ename) AS initcap_name
2 FROM scott.emp;
ENAME INITCAP_NA
--------------- ----------
SMITH Smith
ALLEN Allen
WARD Ward
JONES Jones
MARTIN Martin
BLAKE Blake
CLARK Clark
SCOTT Scott
KING King
TURNER Turner
ADAMS Adams
JAMES James
FORD Ford
MILLER Miller
14 rows selected.
2. Concrete Business Use Cases
2-1. Data Normalization
Manage names in the database in a consistent format.
SELECT customer_id, INITCAP(LOWER(customer_name)) AS formatted_name
FROM customers;
This SQL standardizes all customer names to the “first letter capital, rest lowercase” format.
2-2. Case-Insensitive Search
This is used, for example, when you want to search for a value entered by a user regardless of its case.
SELECT empno,ename,job,deptno
FROM scott.emp
WHERE UPPER(ename) = UPPER('smith');
SQL> SELECT empno,ename,job,deptno
2 FROM scott.emp
3 WHERE UPPER(ename) = UPPER('smith');
EMPNO ENAME JOB DEPTNO
---------- --------------- --------- ----------
7369 SMITH CLERK 20
2-3. Formatting File Names or Code
You can avoid filesystem troubles by consistently formatting file names or code to lowercase.
SELECT LOWER(file_name) AS normalized_file_name
FROM documents;
3. Practical Application Tips
3-1. Performance Optimization
Using UPPER or LOWER in conditions (WHERE) or joins can prevent indexes from being used. This can be avoided with the following methods:
Create a Function-Based Index
CREATE INDEX idx_upper_ename ON scott.emp (UPPER(ename));
This enables high-speed searches even for conditions using UPPER(ename).
Use a Virtual Column Add a virtual column to the table and hold the formatted value there.
ALTER TABLE employees ADD upper_ename GENERATED ALWAYS AS (UPPER(ename)) VIRTUAL;
4. Visual Understanding: Learning String Manipulation with a Flowchart
The following diagram shows how UPPER, LOWER, and INITCAP transform an input string.
Input: "hello world" ↓ UPPER: "HELLO WORLD" LOWER: "hello world" INITCAP: "Hello World"
5. Errors and Points to Note
5-1. Handling NULL
If NULL is passed as an argument to any of these functions, the result will be NULL.
SELECT UPPER(NULL) AS result FROM dual;
Result: NULL
SQL> SELECT UPPER(NULL) AS result FROM dual;
R
-
★NULL
(Note: ★NULL indicates the output is NULL)
5-2. Locale Dependence
The result of uppercase/lowercase conversion depends on the database’s locale settings (NLS_LANG). Testing is important when supporting multiple languages.
6. Practical Problem
Let’s predict the output of the following SQL:
SELECT INITCAP('hello WORLD'), LOWER('ORACLE DATABASE'), UPPER(NULL)
FROM dual;
Expected Result
SQL> SELECT INITCAP('hello WORLD'), LOWER('ORACLE DATABASE'), UPPER(NULL)
2 FROM dual;
INITCAP('HE LOWER('ORACLEDA U
----------- --------------- -
Hello World oracle database
(Note: The third column is NULL)
Summary
- UPPER: Converts to uppercase
- LOWER: Converts to lowercase
- INITCAP: Converts the first letter to uppercase, others to lowercase
By mastering these functions, you can achieve data normalization, improve searches, and standardize formats. Furthermore, you can improve performance by combining them with function-based indexes and virtual columns.
With this, you’ve gained knowledge that’s immediately effective in a professional setting! Next, try testing this in your own database using this article as a reference. Please let us know if you have any questions or opinions!
[reference]
Oracle Database SQL Language Reference, 19c
コメント