Page 111 - DCAP408_WEB_PROGRAMMING

Unit 7: Character Sets, Fonts and the Keyboard

Notes
Example: Show the employee number, name, and department number for employee
John
SELECT empno, ename, deptno
FROM emp
WHERE ename = ‘john’;
no rows selected
The WHERE clause of the first SQL statement specifies the employee name as ‘john’. As all the
data in the EMP table is amassed in uppercase. The name ‘john’ does not locate a match in the
EMP table and as a consequence no rows are chosen.
SELECT empno, ename, deptno
FROM emp
’
WHERE LOWER( ename) = ‘blake ;
EMPNO ENAME DEPTNO
7698 JOHN 30

The WHERE clause of the second SQL statement specifies that the employee name in the EMP
table be converted to lowercase and then be compared to ‘john‘. Since both the names are in
lowercase now, a match is located and one row is chosen.

Task Make distinction between LOWER function and UPPER function.

7.2.2 Character Manipulation Functions

Function Result
CONCAT( ‘Good' , 'String' ) GoodString
SUBSTR( 'String' , 1,3 ) Str
LENGTH( 'String' ) 6
INSTR(' String’, ‘r ’ ) 3
LPAD(sal,10 , '*' ) ******5000

CONCAT. SUBSTR, LENGTH, INSTR, and LPAD are the five character manipulation functions
discussed here.

Concat: It unite values together (You are restricted to using two parameters with CONCAT.)
Substr: It takes out a string of determined length
Length: It exhibits the length of a string as a numeric value
Instr: It locates numeric position of a named character
Lpad: It Pads the character value right-justified

Did u know? RPAD character manipulation function pads the character value left-justified.

LOVELY PROFESSIONAL UNIVERSITY 105