«Back to Home

Oracle Jump Start
Topics

How to Use character Function in Oracle

Character functions
  • CHR(x)
  • CONCAT(string1, string2)
  • INITCAP(string)
  • LOWER(string)
  • UPPER(string)
  • LPAD(char1, n, [char2])
  • RPAD(char1, n, [char2])
  • LTRIM(string, ‘char/s’)
  • RTRIM(string, ’char/s’)
  • REPLACE(string, search_str, [replace_str])
  • SUBSTR(string, m, [n])
  • TRANSLATE(string, from_str, to_str)
  • CHARACTER FUNCTIONS Returning numeric values
    • ASCII(string)
    • INSTR(STRING, CHAR)
    • LENGTH(STRING)
CHR(x)
 
This function returns the character, which has the value equivalent to x in the database character set. CHR and ASCII are the opposite functions.
 
Example
  1. Select CHR (35) a, CHR (102) b, CHR (104) c  
  2. from dual;  
1 

CONCAT(string1, string2)
 
This function returns string1 concatenated with string2. This function is identical to the || operator.
 
Example
  1. select CONCAT('Hello', 'good morning') "wishes"  
  2. from dual;  
2 
INITCAP(string)
 
This function capitalizes the first character of each word in the string.
 
Example
  1. select INITCAP(Emp_name)  
  2. from Employee;  
3

LOWER(string)
 
This function converts the string to lower case.
 
Example
  1. Select LOWER(Emp_name), LOWER(‘xyz’)  
  2. from Employee;  
4

UPPER(string)
 
This function converts the string into upper case.
 
Example
  1. Select UPPER(Emp_name), UPPER('xyz')  
  2. From Employee;  
5

LPAD(char1, n, [char2])
 
This function pads the column or literal value from the left side, to a total width of n character positions. The leading spaces are filled with char2.
 
Example
  1. select LPAD(Emp_name, 10, '#'), LPAD(Emp_name, 10, ' ')  
  2. from Employee;  
6

RPAD(char1, n, [char2])
 
This function pads the column or literal value from the right side, to a total width of n character positions. The trailing spaces are filled with char2.
 
Example
  1. Select RPAD(Emp_name, 10, ‘*’), RPAD(Emp_name, 10, ‘ ’)  
  2. from Employee;  
7

LTRIM(string, ‘char/s’)
 
This functions removes all the blank spaces from the left.
 
Example
  1. Select Emp_name, LTRIM(Emp_name), LTRIM(Emp_name, ‘R’)  
  2. from employee;  
8

RTRIM(string, ‘char/s’)
 
This functions removes all the blank spaces.
 
Example
  1. Select Emp_name, RTRIM(Emp_name), RTRIM(Emp_name, ‘R’)  
  2. from Employee;  
9

REPLACE(string, search_str, [replace_str])
 
This function returns the string with every occurrence of search_str replaced with replace_str.
 
Example
  1. Select REPLACE ('Ball and Battle', 'B', 'c' ) "First"  
  2. from Dual;  
10
  1. Select REPLACE ('Ball and Battle', 'B' ) "Second"  
  2. from Dual;  
11
  1. Select REPLACE ('Ball and Battle', NULL ) "Thrid"  
  2. from Dual;  
12

TRANSLATE(string, from_str, to_str)
 
This functions returns the string in form_str, replaced by the corresponding character in to_str.
Translate is a superset of the functionality, provided by replace.
 
Example
  1. select TRANSLATE ('qwertyuiop', 'uiop', '1234')  
  2. from dual;  
13

Character Function Returning Numeric Values
 
ASCII(string)
 
This function returns the decimal of the first byte of the string in the character set.
 
Example
  1. select ASCII(' ')  
  2. from Dual;  
14
  1. select ASCII('x')  
  2. from Dual;  
15

INSTR(string, char)
 
This function returns the position of the first occurrence of char in the string.
 
Example 
  1. select Emp_name, INSTR(Emp_name, 'a')  
  2. from Employee;  
16
 
LENGTH(string)
 
This functions returns the length of a string. This function applies, only with varchar2.
 
Example
 
  1. Select Emp_name, LENGTH(Emp_name)  
  2. From Employee;  
17

Summary
 
Thus, we learnt, how to use character functions in Oracle with the examples.