Chapter 03-Using Single-Row Functions to Customize Output(01)

Objectives

After completing this lesson,you should be able to do the following:

• Describe various types of functions available in SQL.
• Use character,number,and date functions in SELECT statements.

Lesson Agenda

• Single-row SQL functions
• Character functions
• Number functions
• Working with dates
• Date functions

/*  函数是一定有返回值的，过程可以没有返回值. */

SQL Functions

Two Types of 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

function_name [(arg1,arg2,...)]

Character Functions

Case-Conversion Functions

These functioins convert case for character strings:

Function	Result
LOWER('SQL Course')	sql course
UPPER('SQL Course')	SQL COURSE
INITCAP('SQL Course')	Sql Course

 

 

 

 

Demo-01:

SQL> select LOWER('SQL COURSE') FROM DUAL;

LOWER('SQL
----------
sql course

SQL> SELECT UPPER('Sql Course') FROM DUAL;

UPPER('SQL
----------
SQL COURSE

SQL> SELECT INITCAP('SQL Course') FROM DUAL;

INITCAP('S
----------
Sql Course

 Demo-02:

SQL> SELECT employee_id,last_name,department_id FROM employees WHERE last_name = 'higgins';

no rows selected

SQL> SELECT employee_id,last_name,department_id FROM employees WHERE LOWER(last_name) = 'higgins';

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID
----------- ------------------------- -------------
        205 Higgins                             110

Character-Manipulation Functions

These functions manipulate character strings:

Function	Result
CONCAT('Hello','World')	HelloWorld
SUBSTR('HelloWorld',1,5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld','W')	6
LPAD(salary,10,"*")	*****24000
RPAD(salary,10,'*')	24000*****
REPLACE('Jack and JUE','J','BL')	BLACK and BLUE
TRIM('H' FROM 'HelloWorld')	ellowWorld

 

 

 

 

 

 

 

 

Demo-01:

SQL> SELECT CONCAT('Hello ','World') FROM DUAL;

CONCAT('HEL
-----------
Hello World

SQL> SELECT SUBSTR('HelloWorld',1,5) FROM DUAL;

SUBST
-----
Hello

SQL> SELECT LENGTH('HelloWorld') FROM DUAL;

LENGTH('HELLOWORLD')
--------------------
                  10

SQL> SELECT INSTR('HelloWorld','W') FROM DUAL;

INSTR('HELLOWORLD','W')
-----------------------
                      6

SQL> SELECT LPAD(salary,10,'*') FROM employees where employee_id=100;

LPAD(SALARY,10,'*')
----------------------------------------
*****24000

SQL> SELECT RPAD(salary,10,'*') FROM employees where employee_id=100;

RPAD(SALARY,10,'*')
----------------------------------------
24000*****

SQL> SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;

REPLACE('JACKA
--------------
BLACK and BLUE

SQL> SELECT TRIM('H' FROM 'HelloWorld') FROM DUAL;

TRIM('H'F
---------
elloWorld

 Demo-02 :

SELECT employee_id,CONCAT(first_name,last_name) NAME ,job_id,LENGTH(last_name),INSTR(last_name,'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id,4) = 'REP';

EMPLOYEE_ID NAME                 JOB_ID     LENGTH(LAST_NAME) Contains 'a'?
----------- -------------------- ---------- ----------------- -------------
        202 arcerzhangFay        MK_REP                     3             2
        203 SusanMavris          HR_REP                     6             2
        204 HermannBaer          PR_REP                     4             2
        150 PeterTucker          SA_REP                     6             0
        151 DavidBernstein       SA_REP                     9             0
        152 PeterHall            SA_REP                     4             2
        153 ChristopherOlsen     SA_REP                     5             0
        154 NanetteCambrault     SA_REP                     9             2
        155 OliverTuvault        SA_REP                     7             4
        156 JanetteKing          SA_REP                     4             0
        157 PatrickSully         SA_REP                     5             0

EMPLOYEE_ID NAME                 JOB_ID     LENGTH(LAST_NAME) Contains 'a'?
----------- -------------------- ---------- ----------------- -------------
        158 AllanMcEwen          SA_REP                     6             0
        159 LindseySmith         SA_REP                     5             0
        160 LouiseDoran          SA_REP                     5             4
        161 SarathSewall         SA_REP                     6             4
        162 ClaraVishney         SA_REP                     7             0
        163 DanielleGreene       SA_REP                     6             0
        164 MatteaMarvins        SA_REP                     7             2
        165 DavidLee             SA_REP                     3             0
        166 SundarAnde           SA_REP                     4             0
        167 AmitBanda            SA_REP                     5             2
        168 LisaOzer             SA_REP                     4             0

EMPLOYEE_ID NAME                 JOB_ID     LENGTH(LAST_NAME) Contains 'a'?
----------- -------------------- ---------- ----------------- -------------
        169 HarrisonBloom        SA_REP                     5             0
        170 TaylerFox            SA_REP                     3             0
        171 WilliamSmith         SA_REP                     5             0
        172 ElizabethBates       SA_REP                     5             2
        173 SunditaKumar         SA_REP                     5             4
        174 EllenAbel            SA_REP                     4             0
        175 AlyssaHutton         SA_REP                     6             0
        176 JonathonTaylor       SA_REP                     6             2
        177 JackLivingston       SA_REP                    10             0
        178 KimberelyGrant       SA_REP                     5             3
        179 CharlesJohnson       SA_REP                     7             0

33 rows selected.