CHARACTER FU
CTIO
S
Character functions operate on values of character class datatype, i.e., Char,
Varchar2, Varchar etc. These functions can return either character class
datatype or number classs datatype based on the operation performed on the
input data. Length of a value returned by these functions is limited upto 4000
bytes for varchar2 datatype and 2000 bytes for char datatype. If a function
returns a value that exceedes the length limit, Oracle automatically truncate
the value before returning the result. Some of the SQL built-in character
functions are given in the following table.
1
Single-row Character Functions
CHR CONCAT INITCAP SUBSTR
RTRIM LTRIM TRIM REPLACE
LPAD RPAD UPPER LOWER
TRANSLATE ASCII INSTR LENGTH
2
Examples
SELECT ASCII('Ç')
FROM DUAL;
ASCII ( 'Ç' )
199
SELECT CHR(76) "KARAKTER"
FROM DUAL ;
KAR
L
SELECT ENAME, CONCAT(ENAME, JOB), LENGTH(ename),
INSTR(ename, 'A' )
FROM EMP
WHERE SUBSTR(job ,1 , 5) = UPPER('saLEs');
E
AME CO
CAT(E
AME,JOB) LE
GTH(E
AME) I
STR(E
AME,'A')
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0
SELECT ENAME, CONCAT(ENAME, JOB), LENGTH(ename),
INSTR(ename, 'A' )
FROM EMP
WHERE SUBSTR(ename , -1 , 1) = 'N' ;
E
AME CO
CAT(E
AME,JOB) LE
GTH(E
AME) I
STR(E
AME,'A')
ALLEN ALLENSALESMAN 5 1
MARTIN MARTINSALESMAN 6 2
3
SELECT INSTR('MISSISSIPPI' , 'S' ,5 , 2)
FROM DUAL;
INSTR('MISSISSIPPI','S',5,2)
7
SELECT LPAD( ENAME , 20 , '*' )
FROM emp;
LPAD(E
AME,20,'*')
***************SMITH
***************ALLEN
14 rows selected.
SELECT RPAD( ENAME , 20 , '*' )
FROM EMP;
RPAD(E
AME,20,'*')
SMITH***************
ALLEN***************
14 rows selected.
4
TRIM
SELECT LTRIM('aaaaabbccxXXyyzzaaabbbccc' , 'a') Ltrim
FROM DUAL;
LTRIM
bbccxXXyyzzaaabbbccc
SELECT RTRIM('aaaaabbccxXXyyzzaaabbbcccaaaaaaaa' , 'a') Rtrim
FROM DUAL;
RTRIM
aaaaabbccxXXyyzzaaabbbccc
SELECT trim( ' teknik ' )
FROM dual
TRIM('TEKNIK')
teknik
SELECT trim( ' ' from ' teknik ' )
FROM dual
TRIM(''FROM'TEKNIK
teknik
SELECT trim(leading '0' from '000123')
FROM dual
TRIM(LEAD
123
5
SELECT trim(trailing '1' from 'Tec1h111')
FROM dual
TRIM(TRAILING'1
Tec1h
SELECT trim(both '1' from '123Tech111')
FROM dual
TRIM(BOTH'1'FROM'1
23Tech
6
SOUNDEX
SELECT ENAME
FROM EMP
WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE');
E
AME
SMITH
SELECT SUBSTR('ABCDEFGHIJK' ,4,3) "Alt Metin"
FROM DUAL;
Alt Metin
DEF
Character functions:
A. Case Conversion Functions
• LOWER
• UPPER
• INITCAP
B. Character Manipulation Functions
• CONCAT
• SUBSTR
• LENGTH
• INSTR
• LPAD
• RPAD
• TRIM
7
• LTRIM
• RTRIM
• ASCII
• CHR
• REPLACE
• TRANSLATE
8
Case Conversion Functions
Convert case for character strings
Function Result
LOWER('SQL Course') sql course
UPPER('SQLCourse') SQL COURSE
INITCAP('SQLCourse'} Sql Course
Case Conversion Functions
LOWER, UPPER, and INITCAP are the three case conversion functions.
• LOWER Converts mıxed case ör uppercass character string lo
louercase
• UPPER Converts mısed case ör louercasc clıaracter string to
uppcrcase
• INITCAP Converts first letter of each word to uppercase and
rcmainig Icttcrs to lowercase.
9
Using Case Conversion Functions
Display the employee number, name, and department number for
employee Blake.
SELECT empno, ename, deptno
FROM emp
WHERE ename = 'blake';
no rows selected
The WHERE clause of the first SQL statement specifies the employee name
as ' blake.' Since all the data in the EMP tabls is stored in uppercase. the
name ' blake' does not ffind a match in the EMP table and as a result no rows
are selected.
..........................................................
SELECT empno, ename, deptno
FROM emp
WHERE LOWER( ename) = 'blake’ ;
EMP
O E
AME DEPT
O
7698 BLAKE 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
'blake ' . Since both the names are in lowercase now, a match is found and
one row is selected. The WHERE clause can be rewritten in the following
manner to produce the same result:
10
Using Character Manipulation Functions
Manipulate character strings
Function Result
CONCAT( ‘Good' , 'String' ) GoodString
SUBSTR( 'String' , 1,3 ) Str
LENGTH( 'String' ) 6
INSTR(' String’, ‘r’) 3
LPAD(sal,10 , '*' ) ******5000
Character Manipulation Functions
CONCAT. SUBSTR, LENGTH, INSTR. and LPAD are îhe five character
manipulation functions covered in this lesson.
CONCAT Joins values together (Yon are limited to using two parameters
with CONCAT.)
SUBSTR Extracts a string of determined length
LENGTH Shows the length of a string as a numeric value
INSTR Finds numerîc position of a named character
LPAD Pads the character value right-justified
ote: RPAD character manipulation function pads the character value lelt-
justified.
11
Using the Character Manipulation
Functions (continued)
Example:
The slide example displays ernployee name and job joined together,
length of the employee name, and the numeric position of the letter A in
the employee name, forall employees who are in sales.
SELECT ename, CONCAT( ename, job),
LENGTH(ename),
INSTR(ename, 'A')
FROM emp
WHERE SUBSTR(job, 1, 5) = 'SALES' ;
E
AME CO
CAT(E
AME,JOB) LE
GTH(E
AME) I
STR(E
AME,'A')
ALLEN ALLENSALESMAN 5 1
WARD WARDSALESMAN 4 2
MARTIN MARTINSALESMAN 6 2
TURNER TURNERSALESMAN 6 0
12
Using the Character Manipulation
Functions (continued)
Example
Write a SQL statement to display the data for those employees whose
names end with an N .
SELECT ename, CONCAT(ename, job),
LENGTH(ename), INSTR(ename, 'A')
FROM emp
WHERE SUBSTR(ename, -1, 1) = 'N';
E
AME CO
CAT(E
AME,JOB) LE
GTH(E
AME) I
STR(E
AME,'A')
ALLEN ALLENSALESMAN 5 1
MARTIN MARTINSALESMAN 6 2
13
