Among the essential string manipulation functions for mastering Oracle Database, CONCAT and SUBSTR are extremely useful. This article provides a thorough explanation of these two functions, from basics to applications, using practical examples with the SCOTT schema.
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. CONCAT Function: Concatenating Two Strings
Syntax and Basic Operation
The CONCAT function concatenates two strings.
CONCAT(string1, string2)
string1: The first string you want to concatenate.string2: The second string you want to concatenate.
Practical Example: Concatenating Employee Name and Job Title
Using the SCOTT schema’s EMP table, we will concatenate and display the employee name (ENAME) and job title (JOB).
SELECT CONCAT(ENAME, ' (' || JOB || ')') AS EMP_INFO
FROM EMP;
Execution Result
SQL> SELECT CONCAT(ENAME, ' (' || JOB || ')') AS EMP_INFO
2 FROM EMP;
EMP_INFO
----------------------
SMITH (CLERK)
ALLEN (SALESMAN)
WARD (SALESMAN)
JONES (MANAGER)
MARTIN (SALESMAN)
BLAKE (MANAGER)
CLARK (MANAGER)
SCOTT (ANALYST)
KING (PRESIDENT)
TURNER (SALESMAN)
ADAMS (CLERK)
JAMES (CLERK)
FORD (ANALYST)
MILLER (CLERK)
14 rows selected.
Point
- Limited Flexibility:
CONCATcan only join two strings. To concatenate more than two strings, use the||operator.SQLSELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS FULL_INFO FROM EMP;
2. SUBSTR Function: Extracting Part of a String
Syntax and Basic Operation
The SUBSTR function extracts part of a string starting from a specified position.
SUBSTR(string, start_position, length)
string: The target string.start_position: The extraction start position (starts from 1).length(Optional): The number of characters to extract.
Practical Example: Get the First 3 Characters of an Employee Name
Extract the first 3 characters of the employee name (ENAME).
SELECT ENAME, SUBSTR(ENAME, 1, 3) AS SHORT_NAME
FROM EMP;
Execution Result
SQL> SELECT ENAME, SUBSTR(ENAME, 1, 3) AS SHORT_NAME
2 FROM EMP;
ENAME SHORT_NAME
--------------- ------------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
JAMES JAM
FORD FOR
MILLER MIL
14 rows selected.
3. Applied Examples Combining CONCAT and SUBSTR
Applied Example 1: Combine Part of a Name with the Job Title
Concatenate and display the first 3 characters of the employee name and the job title.
SELECT CONCAT(SUBSTR(ENAME, 1, 3), ' - ' || JOB) AS CUSTOM_INFO
FROM EMP;
Execution Result
SQL> SELECT CONCAT(SUBSTR(ENAME, 1, 3), ' - ' || JOB) AS CUSTOM_INFO
2 FROM EMP;
CUSTOM_INFO
------------------------
SMI - CLERK
ALL - SALESMAN
WAR - SALESMAN
JON - MANAGER
MAR - SALESMAN
BLA - MANAGER
CLA - MANAGER
SCO - ANALYST
KIN - PRESIDENT
TUR - SALESMAN
ADA - CLERK
JAM - CLERK
FOR - ANALYST
MIL - CLERK
14 rows selected.
Applied Example 2: Custom Display with Department Number
Display the first 3 characters of the employee name, job title, and department number in a customized format.
SELECT ENAME, JOB, DEPTNO,
SUBSTR(ENAME, 1, 3) || ' (' || JOB || ') - DEPT ' || DEPTNO AS FULL_INFO
FROM EMP;
Execution Result
SQL> SELECT ENAME, JOB, DEPTNO,
2 SUBSTR(ENAME, 1, 3) || ' (' || JOB || ') - DEPT ' || DEPTNO AS FULL_INFO
3 FROM EMP;
ENAME JOB DEPTNO FULL_INFO
--------------- --------- ---------- --------------------------------------
SMITH CLERK 20 SMI (CLERK) - DEPT 20
ALLEN SALESMAN 30 ALL (SALESMAN) - DEPT 30
WARD SALESMAN 30 WAR (SALESMAN) - DEPT 30
JONES MANAGER 20 JON (MANAGER) - DEPT 20
MARTIN SALESMAN 30 MAR (SALESMAN) - DEPT 30
BLAKE MANAGER 30 BLA (MANAGER) - DEPT 30
CLARK MANAGER 10 CLA (MANAGER) - DEPT 10
SCOTT ANALYST 20 SCO (ANALYST) - DEPT 20
KING PRESIDENT 10 KIN (PRESIDENT) - DEPT 10
TURNER SALESMAN 30 TUR (SALESMAN) - DEPT 30
ADAMS CLERK 20 ADA (CLERK) - DEPT 20
JAMES CLERK 30 JAM (CLERK) - DEPT 30
FORD ANALYST 20 FOR (ANALYST) - DEPT 20
MILLER CLERK 10 MIL (CLERK) - DEPT 10
14 rows selected.
4. Practical Tips and Cautions
Alternative to CONCAT: The || Operator
When concatenating multiple strings, using the || operator is more flexible and efficient. It can be used as follows:
SELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS CONCATENATED_STRING
FROM EMP;
Execution Result
SQL> SELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS CONCATENATED_STRING
2 FROM EMP;
CONCATENATED_STRING
-----------------------------------------------------------------
SMITH - CLERK (20)
ALLEN - SALESMAN (30)
WARD - SALESMAN (30)
JONES - MANAGER (20)
MARTIN - SALESMAN (30)
BLAKE - MANAGER (30)
CLARK - MANAGER (10)
SCOTT - ANALYST (20)
KING - PRESIDENT (10)
TURNER - SALESMAN (30)
ADAMS - CLERK (20)
JAMES - CLERK (30)
FORD - ANALYST (20)
MILLER - CLERK (10)
14 rows selected.
SUBSTR’s Flexibility: Negative Start Position
If the start position is a negative value, it counts from the end of the string. Example:
SELECT SUBSTR('ORACLE', -3, 2) AS RESULT FROM DUAL;
Execution Result
SQL> SELECT SUBSTR('ORACLE', -3, 2) AS RESULT FROM DUAL;
RE
--
CL
5. Practice Problem: Try it Yourself
- Concatenate Employee Name and Department Name Join the SCOTT schema’s
EMPtable andDEPTtable, and display the concatenated employee name and department name.SELECT CONCAT(ENAME, ' - ' || DNAME) AS EMP_DEPT_INFO FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; - Extract the Middle Part of an Employee Name Extract the 2nd through 4th characters of the employee name.
SELECT ENAME, SUBSTR(ENAME, 2, 3) AS MID_NAME FROM EMP;
Summary
- The
CONCATfunction is used when concatenating two strings. However, due to its limited flexibility, consider using the||operator. - The
SUBSTRfunction can extract part of a string and is extremely useful for custom displays and data analysis. - In practice, you can flexibly customize data display by combining these.
Utilize Oracle Database’s powerful string manipulation features to further polish your data processing skills!
[reference]
Oracle Database SQL Language Reference, 19c
コメント