Transcript Lecture 6 - SQL Single Row Number Functions and More Character

Lecture 6
29/1/15
Number functions
• Number functions take numbers as input, change them, and
output the results as numbers.
2
Round()
• This is used to round values up or down and to specify the
number of decimal places. To see this, run
• Select round(123.4567,2), round(123.4567,3),
round(1234.432,1)
from sys.dual;
• This will output
ROUND(123.4567,2) ROUND(123.4567,3) ROUND(1234.432,1)
-------------------------------- ----------------123.46
123.457
1234.4
3
Trunc()
Truncating is similar to rounding. We specify the required number
of decimal places but Oracle doesn’t round up or down. It simply
“chops off” extra digits.
• To see the difference, examine the following select
round(123.456,2), trunc(123.456,2) from sys.dual;
Will return
ROUND(123.456,2) TRUNC(123.456,2)
------------------------- ---------------------------123.46
123.45
4
Sign()
This is used to show if a value is zero, positive, or negative.
™1 is returned if the number is positive
™-1 is returned if the number is negative
™0 is returned if the number is zero
• i.e.
select sign(-11421.215) from sys.dual
will return –1.
5
CEIL()
• Raises the value of the number to the next highest integer.
• For example, Ceil(13213.4214)
Returns
13214
6
Floor()
Lowers the value to the next lowest integer.
For example
Floor(123.89)
Returns
123
7
Power() and others
• POWER
Raises the number given to the power given.
Power(12,2)
Raises 12 to the power of 2.
select power(12,2)
from sys.dual;
Answer:
• Others
There are other numerical functions which Oracle can use.
They are straight forward and easy to use. Other functions include
SQRT (square root), ABS (absolute value), MOD (modulus), LOG
(logarithmic), SIN (sine value), COS (cosine value), TAN (tangent value).
There are several more.
8
Dual
• DUAL is a table owned by the SYS user that contains a single
VARCHAR2 column called DUMMY and a single row with the
value 'X' in it.
• This table is handy when you want to select a pseudocolumn
such as SYSDATE or simply select an expression and only want
to get a single row back.
SQL> DESC sys.dual
Name
Null? Type
------------------------------- -------- ----------------------DUMMY
VARCHAR2(1)
9
More character functions
Substr
• This stands for substring. It returns a part of a string. We specify
which part of the string we want to return.
• For example
select substr('Diploma',2,3)
from sys.dual;
ipl
11
INSTR()
INSTR() is used to find where characters occur in a string.
• For example,
• Instr(‘Diploma’,’o’)
• Would return the number 5.
• select instr('Diploma','oma')
• from sys.dual;
Return?????
12
INSTR() Continued..
• We can change the syntax slightly. So far we have searched for
the 1st occurrence. We can also search for further
occurrences.
For example
select instr('Seventy','e',3)
from sys.dual;
14
Ltrim()
Ltrim() is used to remove leading occurrences of characters.
• If we don’t specify a character, Oracle will remove leading
spaces.
• For example
Running
ltrim(‘ Oracle’)
Will remove the leading spaces.
Ltrim(‘spacious’,’s’)
Will return
pacious (the leading s has been removed)
16
Ltrim() Continued..
select Ltrim('spacious','p')
from sys.dual;
Will return ????
RTRIM
Is the same as LTRIM, except it
trims from the right.
• The order specified for the
leading characters is not
important. For example,
Ltrim(‘spacious’,’ps’) Is the same
as Ltrim(‘spacious’,’sp’)
select rtrim('spacious','su')
from sys.dual;
select Ltrim('spacious','sp')
from sys.dual;
select Ltrim('spacious','ps')
from sys.dual;
Spacio
select rtrim('spacious','soui')
from sys.dual;
spac
19
Another Example
select ltrim(emp_name, 'H')
from employee;
EARNE
BYRNE
WALSH
ARTE
DOHERTY
MARTIN;
21
Another example
SELECT LTRIM(emp_name, 'H' ) "Employee Name"
FROM employee
WHERE emp_name LIKE 'H%';
Employee Name
EARNE
ARTE
22
LPAD
• Lpad is used to “pad” columns/strings
to the left.
• To see this let us take the following
string.
• Let us say that we want the string to
appear as being10 characters in length.
If we say that we want it to be padded
to the left, it would appear like -
23
LPAD Continued…
If we padded with ‘*’it would
look like this
• The syntax for this would be
Lpad(‘diploma’,10,’*’)
• Lpad the word ‘diploma’ so
that it is 10 characters long, with
extra spaces to the left being
filled with *’s.
RPAD
• Rpad, does the same, except
that it pads to the right.
• What will the following
command do ?
• Rpad (‘course’,12)
select Lpad('diploma',10,'*')
from sys.dual;
***diploma
select rpad('diploma',10,'*')
from sys.dual;
diploma***
24
Example
LPAD('tech', 7);
would return ' tech'
LPAD('tech', 2);
would return 'te'
LPAD('tech', 8, '0');
LPAD('tech on the
net', 15, 'z');
LPAD('tech on the
net', 16, 'z');
would return
'0000tech'
would return 'tech
on the net'
would return 'ztech
on the net'
SELECT
LPAD('Good',10,'.'),
RPAD('Good',10,'.')
FROM dual;
.......Good Good.......
25
Length()
• Length() returns the length of a string. For example
• select length('Oracle')
• from sys.dual;
27
Translate
Translate is used to change characters.
select translate('SMITH','I','O')
from sys.dual;
Will change all letter I’s to letter O’s in the string
SMITH.
select translate('HEEEEEEEEEEELP','E','A')
from sys.dual;
• We can also specify more than 1 character to translate.
select translate('HEEEEEEEEEEELP','LP','AA')
from sys.dual;
28
REPLACE()
• Replace is similar to translate. With translate there must be a
match between the number of characters to change and the
number of characters to change with. I.e. we can’t replace X
with TR. We can only replace 1 character with 1 character, 2
with 2, etc.
With replace we can do this.
For example
SELECT replace(job,'ANALYST','BUSANALYST') AS NEWTITLE
from EMPLOYEE;
Will search the job column and replace all occurrences
of ANALYST with BUSANALYST.
29