{[['
']]}
']]}
Using Functions to Customize Output in Oracle & Sql server 2008
Using Functions to Customize Output
After completing this lesson, you should be able to do the following:
§ Describe various types of functions that are available in SQL
§ Use character, number, and date functions in SELECT statements
§ Describe the use of conversion functions
SQL Functions:
Single-row functions:
§ Manipulate data items
§ Accept arguments and return one value
§ Act on each row that is returned
§ Return one result per row
§ May modify the data type
§ Can be nested
§ Accept arguments that can be a column or an expression
Case-Manipulation Functions
Function
|
Result
|
LOWER('SQL Course')
|
sql course
|
UPPER('SQL Course')
|
SQL COURSE
|
INITCAP('SQL Course')
|
Sql Course
|
SELECT patient_id,lname1, lname2 FROM PATIENT
WHERE upper(lname1) = 'HUSEN';
Character-Manipulation Functions
Function
|
Result
|
CONCAT('Hello', 'World')
|
HelloWorld
|
Oracle
|
SELECT patient_id, CONCAT (lname1, lname2) as x FROM PATIENT
|
Sql Server
|
SELECT patient_id, lname1+' '+ lname2 as x
FROM PATIENT
|
SUBSTR('HelloWorld',1,5)
|
Hello
|
Oracle
|
SELECT patient_id,SUBSTR( patient_id,1, 3)
FROM PATIENT
|
Sql Server
|
SELECT patient_id,SUBSTRING( patient_id,1, 3)
FROM PATIENT
|
LENGTH('HelloWorld')
|
10
|
Oracle
|
SELECT patient_id , length(patient_id)as x FROM PATIENT
|
Sql Server
|
SELECT patient_id, aname1, lname1,len( lname1)x
FROM PATIENT
|
Sql Server
|
SELECT patient_id ,DATALENGTH(patient_id)as x FROM PATIENT
|
INSTR('HelloWorld', 'W')
|
6
|
Oracle
|
SELECT patient_id ,instr(patient_id, '9')as x
FROM PATIENT
|
Sql Server
|
SELECT patient_id , CHARINDEX('9',patient_id)as x
FROM PATIENT
|
LPAD(salary,10,'*')
|
*****24000
|
Oracle
|
SELECT patient_id ,LPAD(patient_id, '9','*')as x
FROM PATIENT
|
Sql Server
|
SELECT patient_id,REPLACE(STR(patient_id,10),SPACE(1),'0') 'x'
FROM PATIENT
|
RPAD(salary, 10, '*')
|
24000*****
|
Oracle
|
SELECT patient_id ,rPAD(patient_id, '9','*')as x
FROM PATIENT
|
Sql Server
|
SELECT patient_id , patient_id+REPLICATE('*', 20-len(patient_id)) AS x FROM PATIENT
|
REPLACE
('JACK and JUE','J','BL') |
BLACK and BLUE
|
Oracle
|
SELECT patient_id, lname1, REPLACE(lname1, lname1, 'Ali') AS x FROM PATIENT WHERE (lname1 LIKE 'A__')
|
Sql Server
|
SELECT patient_id, lname1, REPLACE(lname1, lname1, 'Ali') AS x FROM PATIENT WHERE (lname1 LIKE 'A__')
|
TRIM('H' FROM 'HelloWorld')
|
elloWorld
|
Oracle
|
SELECT patient_id, lname1, Trim( 'A' from lname1) AS x FROM PATIENT
|
Sql Server
|
SELECT patient_id, lname1, REPLACE(lname1, 'A', '') AS x FROM PATIENT WHERE (lname1 LIKE 'A__')
|
Number Functions
• ROUND: Rounds value to specified decimal
• TRUNC: Truncates value to specified decimal
• MOD: Returns remainder of division
Function
|
Result
|
ROUND(45.926, 2)
|
45.93
|
TRUNC(45.926, 2)
|
45.92
|
MOD(1600, 300)
|
100
|
§ Sql Server
SELECT Item_Code,Sale_Price ,ROUND(Sale_Price, -4)as ROUND ,
( Sale_Price-(Sale_Price%.001))as Trunc, (Sale_Price %3) as mod FROM ItemFile
§ Oracle
select round(45.923,2), mod (45.923, 2), trunc (45.923,-1) FROM DUAL;
Date Functions
Function
|
Result
|
MONTHS_BETWEEN
|
Number of months between two dates
|
ADD_MONTHS
|
Add calendar months to date
|
NEXT_DAY
|
Next day of the date specified
|
LAST_DAY
|
Last day of the month
|
ROUND
|
Round date
|
TRUNC
|
Truncate date
|
NVL Function
§ This function is used to replace NULL value with another value
- SELECT SUM( NVL (Sales,100)) FROM Sales_Data; àIn Oracle
- SELECT SUM( ISNULL (Sales,100)) FROM Sales_Data; àIn SQL Server
Conditional Expressions
¡ Provide the use of IF-THEN-ELSE logic within a SQL statement
¡ Use two methods:
§ CASE expression
§ DECODE function
¡ CASE expression
The Same in Oracle & Sql Server
SELECT PATIENT_ID,patient_sex,
CASE WHEN PATIENT.patient_sex = '1' THEN 'male'
WHEN PATIENT.patient_sex = '2' THEN 'female' END as Gender
FROM PATIENT
¡ DECODE function
Not Supported By SQL SERVER
select patient_id,patient_sex,
Decode(patient_sex, '1', 'Male' , '2', 'FeMale',patient_sex) as x
FROM patient;
Post a Comment