4/2/16. ltrim() is used to remove leading occurrences of characters. if we dont specify a character,...
TRANSCRIPT
![Page 1: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/1.jpg)
Lecture 44/2/16
![Page 2: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/2.jpg)
2
Ltrim() is used to remove leading occurrences of characters.• If we don’t specify a character, Oracle will remove leading spaces.• For exampleRunning ltrim(‘ Oracle’)Will remove the leading spaces.
Ltrim(‘spacious’,’s’)Will returnpacious (the leading s has been removed)
Ltrim()
![Page 3: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/3.jpg)
![Page 4: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/4.jpg)
![Page 5: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/5.jpg)
5
select Ltrim('spacious','p')from sys.dual;Will return ????
• The order specified for the leading characters is not important. For example,Ltrim(‘spacious’,’ps’) Is the same as Ltrim(‘spacious’,’sp’)
select Ltrim('spacious','sp')from sys.dual;
select Ltrim('spacious','ps')from sys.dual;
RTRIMIs the same as LTRIM, except it trims from the right.
select rtrim('spacious','su')from sys.dual;Output - Spacio
select rtrim('spacious','soui')from sys.dual;Output - spac
Ltrim() Continued..
![Page 6: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/6.jpg)
![Page 7: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/7.jpg)
7
select ltrim(emp_name, 'H') from employee;
EARNEBYRNEWALSHARTEDOHERTYMARTIN;
Another Example
select ltrim(ename, 'M') from emp;
![Page 8: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/8.jpg)
8
SELECT LTRIM(ename, 'M' ) "Employee Name" FROM emp WHERE ename LIKE 'M%';
Employee NameILLERARTIN
Another example
![Page 9: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/9.jpg)
9
Another example SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name" FROM products WHERE product_name LIKE 'Monitor%';
PRODUCT_NAME Short Name-------------------- ---------------Monitor 17/HR 17/HRMonitor 17/HR/F 17/HR/FMonitor 17/SD 17/SDMonitor 19/SD 19/SDMonitor 19/SD/M 19/SD/MMonitor 21/D 21/DMonitor 21/HR 21/HRMonitor 21/HR/M 21/HR/MMonitor 21/SD 21/SDMonitor Hinge - HD Hinge - HDMonitor Hinge - STD Hinge - STD
![Page 10: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/10.jpg)
10
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 -
![Page 11: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/11.jpg)
11
If we padded with ‘*’it would look like this • The syntax for this would beLpad(‘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)
LPAD Continued…
select Lpad('diploma',10,'*')from sys.dual;
***diploma
select rpad('diploma',10,'*')from sys.dual;
diploma***
![Page 12: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/12.jpg)
12
LPAD('tech', 7); ' tech'LPAD('tech', 2); 'te'LPAD('tech', 8, '0'); '0000tech'LPAD('tech on the net', 15, 'z'); 'tech on the net'LPAD('tech on the net', 16, 'z'); 'ztech on the net'
SELECT LPAD('Good',10,'.'), RPAD('Good',10,'.') FROM dual;
.......Good Good.......
Example
![Page 13: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/13.jpg)
![Page 14: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/14.jpg)
14
Length() returns the length of a string. For example
select length('Oracle') from sys.dual;
Length()
![Page 15: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/15.jpg)
Answer 6
![Page 16: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/16.jpg)
16
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 stringSMITH.
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;
HEEEEEEEEEEEAA
Translate
![Page 17: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/17.jpg)
17
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.
For exampleSELECT replace(job,'ANALYST','BUSANALYST') AS NEWTITLE from EMPLOYEE;
Will search the job column and replace all occurrences of ANALYST with BUSANALYST.
NEWTITLECLERKCLERKSALESMANPRESIDENTBUSANALYSTMANAGERMANAGERSALESMANMANAGERSALESMANSALESMANCLERK
REPLACE()
![Page 18: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/18.jpg)
![Page 19: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/19.jpg)
19
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 pseudo column such as SYSDATE or simply select an expression and only want to get a single row back.
SQL> DESC sys.dualName Null? Type------------------------------- -------- -----------------------DUMMY VARCHAR2(1)
Dual
![Page 20: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/20.jpg)
20
Number functions take numbers as input, change them, and output the results as numbers.
Number functions
![Page 21: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/21.jpg)
21
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
Round()
![Page 22: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/22.jpg)
22
Truncating is similar to rounding. We specify therequired 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 returnROUND(123.456,2) TRUNC(123.456,2)------------------------- ----------------------------123.46 123.45
Trunc()
![Page 23: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/23.jpg)
23
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.dualwill return –1.
Sign()
![Page 24: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/24.jpg)
24
• Raises the value of the number to the next highest integer.• For example, Ceil(13213.4214)
Returns13214
CEIL()
![Page 25: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/25.jpg)
25
Lowers the value to the next lowest integer.For exampleFloor(123.89)Returns 123
Floor()
![Page 26: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/26.jpg)
26
• POWERRaises 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:
• OthersThere are other numerical functions which Oracle can use.They are straight forward and easy to use. Other functions includeSQRT (square root), ABS (absolute value), MOD (modulus), LOG (logarithmic), SIN (sine value), COS (cosine value), TAN (tangent value).There are several more.
Power() and others
![Page 27: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/27.jpg)
27
The Four Main DBMS Components
Language to create andmodify data
Data Definition Language
Language toprocess and update data
DataManipulation Language
An electronicdocumentthat providesdetailedinformationabout eachand everypiece of datain the database
Data Dictionary
Software thatgenerates reportsand makes the database user-friendly
Reports and Utilities
DBMS
![Page 28: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/28.jpg)
28
Revision Data Manipulation Language (DML) Select *
◦ From Table Name◦ Where◦ Order by◦ Wildcard ‘%’
Single Row Functions◦ Character Functions – Lower(), Upper(), Instr(),
Substr(), Initcap(), Concat()/||, lpad(), rpad(), ; ltrim(), rtrim()
◦ Number Functions – Round(), Trunc(), Ceil(), Floor(), Power()
![Page 29: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/29.jpg)
29
Revision Data Definition
Language (DDL)◦ Create
CREATE TABLE EMP2 (EMP_NO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9), MGR NUMBER(4),HIREDATE DATE, SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2));
◦ Insert INSERT INTO table
(column1, column2, ... ) VALUES (expression1, expression2, ... );
◦ Update UPDATE table SET column1
= expression1, column2 = expression2, ... WHERE conditions;
◦ Delete DELETE FROM table WHERE
conditions;◦ Drop
DROP TABLE Table Name;
![Page 30: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/30.jpg)
30
Date functions
SYSDATE is a pseudo-column. It is used to retrieve the current date and time.
We normally select sysdate from a dummy table called sys.dual.
◦ Select sysdate from sys.dual;
20-JAN-14
![Page 31: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/31.jpg)
31
Arithmetic on dates
We can add and
subtract from dates. The syntax would be, as an example◦ Select hiredate-7
from employee;
This would return the hiredate minus 1 week (7 days)
select hiredate from employee;07-JAN-1120-FEB-0911-OCT-0720-JAN-1228-JUN-1107-SSelect hiredate-7 from employee;31-DEC-1013-FEB-0904-OCT-0713-JAN-1221-JUN-1131-AUG-13EP-13
![Page 32: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/32.jpg)
32
Working with months
MONTHS_BETWEEN This tells us the number of
months between 2 dates. For example, to determine how
many months an employee has worked is the company, we would run
ADD_MONTHS We use this to add a number of
months to a date. What does the following do?
select hiredate, add_months(hiredate,-3), add_months(hiredate,3) from employee;
Select months_between(sysdate,hiredate)"WITH COMPANY", emp_name from employee;
36.43413306451612903225806451612903225806 HEARNE59 BYRNE75.30510080645161290322580645161290322581 WALSH24 HARTE30.75671370967741935483870967741935483871 DOHERTY4.43413306451612903225806451612903225806 MARTIN
![Page 33: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/33.jpg)
33
Select trunc(months_between(sysdate,hiredate))"WITH COMPANY", emp_name from employee;36 HEARNE
59 BYRNE75 WALSH24 HARTE63 CASEY23 MURRAY30 DOHERTY4 MARTIN
![Page 34: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/34.jpg)
34
Continued..select hiredate, add_months(hiredate,3) from employee;07-JAN-1107-APR-1120-FEB-0920-MAY-0911-OCT-07 11-JAN-0820-JAN-1220-APR-1228-JUN-1128-SEP-1107-SEP-1307-DEC-13
select hiredate, add_months(hiredate,-3), add_months(hiredate,3) from employee;
07-JAN-1107-OCT-10 07-APR-1120-FEB-0920-NOV-08 20-MAY-0911-OCT-07 11-JUL-07 11-JAN-0820-JAN-1220-OCT-11 20-APR-1228-JUN-1128-MAR-11 28-SEP-1107-SEP-1307-JUN-1307-DEC-13
![Page 35: 4/2/16. Ltrim() is used to remove leading occurrences of characters. If we dont specify a character, Oracle will remove leading spaces. For example Running](https://reader033.vdocuments.us/reader033/viewer/2022052917/5a4d1b957f8b9ab0599c34d8/html5/thumbnails/35.jpg)
35
Working with days
NEXT_DAY To find the next
occurrence of a particular day, we run next_day. For example,◦ select sysdate,
next_day(sysdate,'FRIDAY') from sys.dual;
will tell us next Friday’s date.
20-JAN-1424-JAN-14
LAST_DAY It is common in
companies that pay day is the last day of the month. To determine this, we use last_day.◦ select last_day(sysdate)
from sys.dual; will give us the date of
the last day of this month.
31-JAN-14