Using Character Functions in Queries

Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated. The character functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The functions UPPER, INITCAP, and LOWER display their character arguments in uppercase, initial capital, and lowercase, respectively.

The query in the following example displays LAST_NAME in uppercase, FIRST_NAME with the first character in uppercase and all others in lowercase, and EMAIL in lowercase.

Changing the Case of Character Data

SELECT UPPER(LAST_NAME) "Last",
INITCAP(FIRST_NAME) "First",
LOWER(EMAIL) "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;

Result:

Last                      First                E-Mail
------------------------- -------------------- -------------------------
FAVIET                    Daniel               dfaviet
SCIARRA                   Ismael               isciarra
CHEN                      John                 jchen
URMAN                     Jose Manuel          jmurman
POPP                      Luis                 lpopp
GREENBERG                 Nancy                ngreenbe
 
6 rows selected.

The functions LTRIM and RTRIM trim characters from the left and right ends of their character arguments, respectively. The function TRIM trims leading zeros, trailing zeros, or both.

The query in the following example finds every clerk in the EMPLOYEES table and trims '_CLERK' from the JOB_ID, displaying only the characters that identify the type of clerk.

Trimming Character Data

SELECT LAST_NAME,
RTRIM(JOB_ID, '_CLERK') "Clerk Type"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Clerk Type
------------------------- ----------
Atkinson                  ST
Baida                     PU
Bell                      SH
Bissot                    ST
Bull                      SH
Cabrio                    SH
Chung                     SH
Colmenares                PU
Davies                    ST
Dellinger                 SH
Dilly                     SH
 
LAST_NAME                 Clerk Type
------------------------- ----------
Everett                   SH
Feeney                    SH
...
LAST_NAME                 Clerk Type
------------------------- ----------
Walsh                     SH
 
45 rows selected.

The functions LPAD and RPAD pad their character arguments on the left and right, respectively, with a specified character (the default character is a space).

The query in the following example displays FIRST_NAME and LAST_NAME in 15-character columns, blank-padding FIRST_NAME on the left and LAST_NAME on the right.

Padding Character Data

SELECT LPAD(FIRST_NAME,15) "First",
RPAD(LAST_NAME,15) "Last"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY FIRST_NAME;

Result:

First           Last
--------------- ---------------
         Daniel Faviet
         Ismael Sciarra
           John Chen
    Jose Manuel Urman
           Luis Popp
          Nancy Greenberg
 
6 rows selected.

The SUBSTR function accepts as arguments a string, a character position, and a length, and returns the substring that starts at the specified position in the string and has the specified length.

The query in the following example uses SUBSTR to abbreviate FIRST_NAME to first initial and to strip the area code from PHONE_NUMBER.

Extracting Substrings from Character Data

SELECT SUBSTR(FIRST_NAME, 1, 1) || '. ' || LAST_NAME "Name",
SUBSTR(PHONE_NUMBER, 5, 8) "Phone"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

Name                         Phone
---------------------------- --------
J. Chen                      124.4269
D. Faviet                    124.4169
N. Greenberg                 124.4569
L. Popp                      124.4567
I. Sciarra                   124.4369
J. Urman                     124.4469
 
6 rows selected.

The REPLACE function replaces one substring with another.

The query in the following example uses the SUBSTR function in the WHERE clause to select the employees whose JOB_ID starts with 'SH', and uses the REPLACE function to replace 'SH' with 'SHIPPING' in each such JOB_ID.

Replacing Substrings in Character Data

COLUMN "Job" FORMAT A15;
SELECT LAST_NAME,
REPLACE(JOB_ID, 'SH', 'SHIPPING') "Job"
FROM EMPLOYEES
WHERE SUBSTR(JOB_ID, 1, 2) = 'SH'
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Job
------------------------- ---------------
Bell                      SHIPPING_CLERK
Bull                      SHIPPING_CLERK
Cabrio                    SHIPPING_CLERK
Chung                     SHIPPING_CLERK
Dellinger                 SHIPPING_CLERK
Dilly                     SHIPPING_CLERK
Everett                   SHIPPING_CLERK
Feeney                    SHIPPING_CLERK
Fleaur                    SHIPPING_CLERK
Gates                     SHIPPING_CLERK
Geoni                     SHIPPING_CLERK
 
LAST_NAME                 Job
------------------------- ---------------
Grant                     SHIPPING_CLERK
Jones                     SHIPPING_CLERK
McCain                    SHIPPING_CLERK
OConnell                  SHIPPING_CLERK
Perkins                   SHIPPING_CLERK
Sarchand                  SHIPPING_CLERK
Sullivan                  SHIPPING_CLERK
Taylor                    SHIPPING_CLERK
Walsh                     SHIPPING_CLERK
 
20 rows selected.

Using Character Functions in Queries

Related Topics