Resume SQL1 Pertemuan 4

FUNGSI - FUNGSI SQL JOIN

Case-Manipulation Functions

These functions convert case for character strings:

Using Case-Manipulation Function

• Display the employee number, name, and department number for employee Higgins:

Character-Manipulation Functions

These functions manipulate character strings:

 

Following are the single-row functions (functions that operate on single rows only and
return one result per row) that perform case conversion and/or character manipulation

• Character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query.
• Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query

1. UPPER: Converts alpha characters to upper case
2. Lower: Converts alpha characters to upper case
3. INITCAP: Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase.

Examples:
SELECT UPPER(title) AS "Rock Stars”
FROM My_cds
WHERE cd_number = 60; [converts title into upper case for cds with cd_ number = 60
                                           in My_cds table]
SELECT title
FROM d_cds
WHERE INITCAP(title) = 'Carpe Diem';
 

Case Conversion Functions:

• CONCAT: Concatenates [joins] the first character value to the second character value; equivalent to concatenation operator (||).
• SUBSTR: Returns specific characters from character value starting at a specific character position and going specified character positions long
• INSTR: Returns the numeric position of a named string.
• LENGTH: Returns the number of characters in the expression
• LPAD: Pads the left side of a character, resulting in a right-justified value
• RPAD: Pads the right-hand side of a character, resulting in a left- justified value.
• TRIM: Removes all specified characters from either the beginning or the ending of a string.
• REPLACE: Replaces a sequence of characters in a string with another set of characters.

SELECT LOWER (last_name)||LOWER(SUBSTR(first_name,1,1)) AS "User Name"
FROM f_staffs;

|| can be used in place of CONCAT function

SELECT *
FROM employees
WHERE last_name = :l_name [:substitution variable used for taking input from the user]
We use * when we want to select all records.

Number functions: These functions accept numeric input and return numeric values.

1. TRUNC: Used to terminate the column, expression, or value to a specified number of decimal places
2. MOD: Returns the remainder of a division. E.g. MOD (100, 2)
3. ROUND: Rounds the column, expression, or value to a set number of decimal places.

Syntax is

ROUND (column|expression, decimal places)
TRUNC (column/expression, decimal places)

Round (458.789, 2)
FROM DUAL;                        [ will give the output 458.79]
To round digits to the left of decimal place we use minus sign with the number.
e.g. ROUND(458.79, -2)

STIKOM SURABAYA
Riko Dwi Christian
D3 - Manajemen Informatika