Character Functions

Case-Manipulation Functions :

• These functions convert case for character strings

LOWER :

• Converts mixed-case or uppercase character strings to lowercase

UPPER :

• Converts mixed-case or lowercase character strings to uppercase

INITCAP :

• Converts the first letter of each word to uppercase and remaining letters to lowercase

SELECT 'The job id for '||UPPER(ename)||' is ‘ ||LOWER(job) AS "EMPLOYEE DETAILS“ FROM employees;

Using Case-Manipulation Functions :

• Display the employee number, name, and department number for employee Scott
• SELECT empno, ename, deptno FROM emp WHERE ename = ‘Scott';
  no rows selected
• SELECT empno, ename, deptno FROM emp WHERE INITCAP(ename) = ‘Scott’;
  

Character-Manipulation Functions :

• These functions manipulate character strings

Using the Character-Manipulation Functions :

SELECT empno, 'NAME IS: ‘||ename as NAME, job, LENGTH (ename), POSITION(‘A’ IN ename) AS "Contains 'a'?" FROM emp WHERE SUBSTR(job, 1,3) = 'MAN';

The slide example displays employee first names and a string joined together, the length of the employee name, and the numeric position of the letter a in the employee name for all employees who have the string MAN contained in the job ID starting at the first position of the job ID.