Lower Function:
Ø It converts alpha character values
to lower case.
Ø The return value has the same data
type as argument char type (Char or Varchar2)
Syntax: lower (column/expression)
Ex:
- Select ’ORACLE CORPORATION’
string, LOWER (’ORACLE CORPORATION’) lower from dual;
- Select ename, lower(ename)
lower from emp;
- Select ename, job, sal from emp
where lower(job) = ’manager’;
Upper Function:
Ø It converts the alpha character
values to upper case.
Ø The return value has the same data
type as the argument char.
Syntax: Upper
(column/expression)
Ex:
- Select ’oracle corporation’
string, upper (’oracle corporation’) upper from dual;
- Select ename, lower(ename),
upper(ename) from emp;
- Select ename,job, sal from emp
where job = upper (’manager’);
- Select ename, job , sal from
emp where job = upper(lower (’MANAGER’));
- Select upper (’the’ ||ename||’
basic salary is Rupees ’||Sal) from emp where job IN (’MANAGER’,upper (’clerk’))
order by sal DESC;
INITCAP Function:
Ø It converts the alpha character
values into uppercase for the first letter of each word, keeping all other
letters in lower case.
Ø Words are delimited by white space
or characters that are not alphanumeric.
Syntax: initcap
(column/expression)
Ex:
- Select ’oracle corporation’
string, initcap (’oracle corporation’) initcap from dual;
- Select ename, upper(ename),
lower(ename),initcap(ename) from emp;
CONCAT Function:
Ø It concatenates the first characters
value to the second character value.
Ø It accepts only two parameters.
Ø It returns the character data type.
Syntax: Concat
(column1/expr1 , column2/expr2)
Ex:
- Select ’Oracle’ string1,
’corporation’ string2, concat (’oracle’,’corporation’) concat from dual;
- Select ename, job,
concat(ename,job) concat from emp;
- Select concat (’The Employee
Name is ’, initcap(ename)) info from emp;
- Select
concat(concat(initcap(ename), ’ is a ’), job) job from emp;
Length Function:
Ø Returns the number of characters in
a value
Ø If the string has data type char,
the length includes all trailing blanks.
Ø If the string is null, it returns
null.
Syntax: length
(column/expression)
Ex:
- Select ’Oracle’ string, length
(’oracle’) length from dual;
- Select length(ename)||’
characters exit in ’||INITCAP(ename)||’ s name.’ as “names and lengths”
from emp;
- Select initcap(ename), job from
emp where length(job) = 7;
Sub String Function:
Ø Returns specified characters from
character value, starting from a specified position ‘m’ to ‘n’ characters long.
Syntax: substr(col/expr,
m,n)
Points to remember:
Ø If “m” is 0, it is treated as 1.
Ø If “m” is positive, oracle counts
from the beginning of string to find the first character.
Ø If “m” is negative, oracle counts
backwards from the end of the string.
Ø If “n” is omitted, oracle returns
all characters to the end of string.
Ø If “n” is less than 1 or 0, A NULL
is returned.
Ø Floating point numbers passed as
arguments to substr are automatically converted to integers.
Ex:
- Select ’ABCDEFGH’ string,
substr (’ABCDEFGH’,3,4) substring from dual;
- Select ’ABCDEFGH’ string,
substr (’ABCDEFGH’,-5,4) substring from dual;
- Select ’ABCDEFGH’ string,
substr (’ABCDEFGH’,0,4) substring from dual;
- Select ’ABCDEFGH’ string,
substr (’ABCDEFGH’,4) substring from dual;
- Select ’ABCDEFGH’ string,
substr(’ABCDEFGH’,4,0) substring from dual;
- Select ’ABCDEFGH’ string,
substr(’ABCDEFGH’,4,-2) substring from dual;
- Select ename, job from emp
where substr(job,4,3) = upper(’age’);
- Select
concat(initcap(ename),concat(’ is a ’, concat(initcap(substr(job, 1, 3)),
’Eater’)))
From emp where substr(job,4,3) =
upper(’Age’);
INSTRING Function:
Ø It returns the numeric position of a
named character.
Syntax: instr(column/expression
, char, m, n)
Ø The INSTR functions search string
for substring that is supplied.
Ø The function returns an integer
indicating the position of the character in string that is the first character
of this occurrence.
Ø Searches for column or expression
beginning with its ‘m’th character for the ‘n’th occurrence of char2, and
returns the position of the character in char1, that is the first character of
this occurrence.
Ø ‘m’ can be positive or negative, if
negative searches backward from the end of column or expression.
Ø The value of ‘n’ should be positive.
Ø The default values of both ‘m’ and
‘n’ are 1.
Ø The Return value is relative to the
beginning of char1 regardless of the value of ‘m’, and is expressed in
characters.
Ø If the search is unsuccessful, the
return value is Zero.
Ex:
- Select ’STRING’ string, instr(’STRING’,’R’)
instring from dual;
- Select ’CORPORATE FLOOR’
string, instr(’CORPORATE FLOOR’,’OR’,3,2) instring from dual;
- Select ’CORPORATE FLOOR’
string, instr(’CORPORATE FLOOR’,’OR’,-3,2) instring from dual;
- Select job, instr(job,’A’,1,2)
position from emp where job = ’MANAGER’;
- Select job, instr(job, ’A’,2,2)
position from emp where job = ’MANAGER’;
- Select job, instr(job, ’A’,3,2)
position from emp where job = ’MANAGER’;
- Select job, instr(job, ’A’,2)
position from emp where job = ’MANAGER’;
Lpad Function:
Ø Pads the character value right
justified to a total width of ‘n’ character positions.
Ø The default padding character is
space.
Syntax: lpad(char1, n,
‘char2’)
Ex:
- Select ’page 1’ string, lpad (’page
1’,15,’*’) lpadded From dual;
- Select ’page 1’ string, lpad(’page
1’,15) lpadded From dual;
- Select ename, lpad(ename,10,’-’)
lpadded from emp where sal >= 2500;
Rpad Function:
Ø Pads the character value left
justified to a total width of ‘n’ character positions.
Ø The default padding character is
space.
Syntax: Rpad(char1,
n,’char2’)
Ex:
- Select ’page 1’ string, rpad (’page
1’,15,’*’) rpadded from dual;
- Select ’page 1’ string, rpad (’page
1’,15) rpadded from dual;
- Select ename,rpad(ename,10,’-’)
rpadded from emp where sal >= 2500;
- Select Ename,
lpad(ename,10,’-’) lpadded, rpad(ename,10,’-’) rpadded from emp;
- Select Ename, lpad(rpad(ename,10,’-’)
centered from emp;
Ltrim Function:
Ø It enables to trim heading
characters from a character string.
Ø All the leftmost characters that
appear in the set are removed.
Syntax: ltrim(char, set)
Ex:
- Select ’xyzXxyLAST WORD’ string,
ltrim(’xyzXxyLAST WORD’, ’xy’) ltrimmed from dual;
- Select job, ltrim(job,’MAN’) ltrimmed
from emp where job like ’MANAGER’;
Rtrim Function:
Ø It enables the training of trailing
characters from a character string.
Ø All the right most characters that
appear in the set are removed.
Syntax: rtrim(char, set)
Ex:
- Select ’BROWNINGyxXxy’ string,
rtrim(’BROWINGyxXxy’,’xy’) rtrimmed from dual;
- Select rtrim(job,’ER’), job
from emp where ltrim(job, ’MAN’) like ’GER’;
Trim Function:
Ø It enables to trim heading or trailing
characters or both from a character string.
Ø If leading is specified concentrates
on leading characters.
Ø If trailing is specified
concentrates on trailing characters.
Ø If both or none is specified
concentrates both on leading and trailing.
Syntax: trim(leading/trailing/both,
trim char from trim source)
Ex:
- Select ’MITHSS’ string, trim(’S’
from ’MITHSS’) trimmed from dual;
- Select ’SSMITH’ string, trim (’S’
from ’SSMITH’) trimmed from dual;
- Select ’SSMITHSS’ string,
trim(’S’ from ’SSMITHSS’) trimmed from dual;
- Select ’SSMITHSS’ string,
trim(leading ’S’ from ’SSMITHSS’) trimmed from dual;
- Select ’SSMITHSS’ string,
trim(trailing ’S’ from ’SSMITHSS’) trimmed from dual;
- Select ’SSMITHSS’ string,
trim(both ’S’ from ’SSMITHSS’) trimmed from dual;
Replace Function:
Ø It returns the every occurrence of
search string replaced by the replacement string.
Ø If the replacement string is omitted
or null, all occurrence of search string are removed.
Ø It substitutes one string for
another as well as removes characters strings.
Syntax: replace(char,
search_str, replace_str)
Ex:
- Select ’JACK AND JUE’ string,
replace(’JACK AND JUE’,’J’,’BL’) Replaced from dual;
- Select ’JACK AND JUE’ string,
replace(’JACK AND JUE’,’j’,’BL’) Replaced from dual;
- Select Ename, replace(job,’MAN’,’DAM’)
replaced from emp where job=’MANAGER’;
- Select job, replace(job, ’p’)
from emp where job=’PRESIDENT’;
- Select job, replace(job, ’MAN’,
’EXECUTIVE’) from emp where job=’SALESMAN’;
Translate Function:
Ø It used to translate character by
character in a string.
Syntax: Translate (CHAR, FROM,
To)
Ø It returns a CHAR with all
occurrences of each character in ‘FROM’ replaced by its corresponding character
in ‘TO’.
Ø Characters in CHAR that are not in FROM
are not replaced.
Ø The argument FROM can contain more
characters than TO.
Ø If the extra characters appear in
Char, they are removed from the return value.
Ex:
- Select job, translate(job, ’p’,’
’) from emp where job = ’PRESIDENT’;
- Select job translate(job,
’MN’,’DM’) from emp where job = ’MANAGER’;
- Select job, translate(job, ’A’,’O’)
from emp where job=’SALESMAN’;
CHR Function:
Ø It returns a character having the
ASCII equivalent to ‘n’.
Ø It returns the equivalent for ‘n’ in
database character set or national character set.
Syntax: CHR(n)
Ex:
- Select
chr(67)||chr(65)||chr(84) sample from dual;
ASCII Function:
Ø It returns the ASCII representation
in the character database set of the first characters of the char.
Syntax: ASCII(char)
- Select ASCII(’A’), ASCII(’APPLE’)
from dual;