Using Character Functions

Oracle Database XE provides a set of character functions that you can use in your SQL statements to customize the character values. With character functions, you can perform operations that change the case, remove blanks, extract substrings from, replace substrings in, and concatenate character data.

Example: Using SQL Character Functions shows the use of some character functions on character data.

Using SQL Character Functions

-- you can use the UPPER function to display uppercase data, LOWER for lowercase
SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;

-- you can use the INITCAP function to display uppercase only the first letter
SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;

-- you can use RTRIM and LTRIM to remove spaces from the beginning or end of 
-- character data. Note the use of concatenation operator || to add a space
SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;

-- you can use TRIM to remove spaces from both the beginning and end
SELECT employee_id, TRIM(last_name) || ', ' || TRIM(first_name) FROM employees;

-- you can use RPAD to add spaces on the right to line up columns
-- in this case, spaces are added to pad the last_name output to 30 characters
SELECT employee_id, RPAD(last_name, 30, ' '), first_name FROM employees;

-- use SUBSTR to select a substring of the data, in the following only 
-- the characters from 1 to 15 are selected from the last_name
SELECT employee_id, SUBSTR(last_name, 1, 10) FROM employees;

-- use LENGTH to return the number of characters in a string or expression
SELECT LENGTH(last_name) FROM employees;

-- use REPLACE to replace characters in a string or expression
SELECT employee_id, REPLACE(job_id, 'SH', 'SHIPPING') FROM employees
   WHERE SUBSTR(job_id, 1, 2) = 'SH';