Macam-Macam Fungsi dalam SQL

Setiap bahasa pemrograman biasanya memiliki fungsi-fungsi yang memudahkan pengguna-nya, tidak terkecuali SQL. Beberapa fungsi yang tertulis dalam artikel ini merupakan fungsi standar dari bahasa pemrograman SQL.

1. Concatenation

Syntax:
value1 || value2

Digunakan untuk menggabungkan dua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi data bertipe string. Pernyataan berikut ini mengkombinasikan nilai-nilai kolom dengan karakter literal.

SELECT
  LastName || ", " || FirstName
FROM Names

Fungsi ini tidak dapat digunakan dengan kolom bertipe Memo atau Blob.

2. LOWER

Syntax:
LOWER(column_reference)

Mengkonversi semua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi huruf kecil. Database seperti SQL Server memakai pernyataan LOWERCASE. Pernyataan SELECT berikut mengubah nilai-nilai dalam kolom Name menjadi huruf kecil.

SELECT
  LOWER(Name)
FROM Country

Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi LOWER hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

3. UPPER

Syntax:
UPPER(column_reference)

Merupakan kebalikan dari fungsi LOWER, yaitu mengkonversi semua nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) menjadi huruf kapital. Sebagai contoh, pernyataan berikut akan menyaring data berdasarkan nilai kolom Name yang dikonversi ke dalam huruf kapital yang diawali karakter PE (nilai lateral).

SELECT
  Name,
  Capital,
  Continent
FROM Country
WHERE UPPER(Name) LIKE UPPER("Pe%")

Sama seperti fungsi LOWER, perubahan nilai akibat fungsi ini tidak akan disimpan jika diakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan.

4. SUBSTRING

Syntax:
SUBSTRING(column_reference FROM start_index [FOR length]

Mengutip substring dari nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang dinyatakan dalam kolom yang dimaksud (colom_reference dalam syntax di atas). Klausa FROM adalah posisi karakter untuk memulai pengutipan substring. Karakter pertama nilai sumber diberi indeks 1 (satu). Dan kata kunci FOR menyatakan banyaknya substring yang ingin dikutip. Jika kata kunci FOR tidak dideklarasikan maka substring dimulai dari posisi yang dinyatakan oleh kata kunci FROM sampai akhir string. Contoh berikut mengutip tiga huruf dimulai dari huruf kedua, hasilnya adalah “BCD” jika nilai asalnya adalah “ABCDE” dan dimasukkan dalam kolom lietral bernama Sub

SELECT
  SUBSTRING("ABCDE" FROM 2 FOR 3) AS Sub
FROM Country

Dan pernyataan SELECT berikut hanya akan menampilkan huruf kedua dan seterusnya dari kolom Name, artinya nilai “Belgium” dalam kolom tersebut akan ditampilkan sebagai “elgium”.

SELECT
  SUBSTRING(Name FROM 2)
FROM Country

Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi SUBSTRING hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

5. TRIM

Syntax :
TRIM([LEADING|TRAILING|BOTH] [trimmed_char] FROM column_reference)

Untuk menghapus karakter pertama, terakhir atau kedua-duanya (pertama dan terkahir) dari nilai karakter (nilai kolom, literal, parameter, atau nilai hasil perhitungan). Berkut ini deskripsi parameter yang ada dalam fungsi TRIM :

Parameter	Deskripsi
LEADING	Menghapus karakter terakhir sisi kiri string
TRAILING	Menghapus karakter terakhir sisi kanan string
BOTH	Menghapus karakter terakhir kedua sisi string

Case-sensitivity (huruf besar/kecil mempunyai nilai berbeda) berlaku dalam pernyataan parameter tersebut. FROM merupakan nilai karakter sumber yang akan dihapus. Tabel berikut menunjukkan hasil dari variasi deklarasi fungsi TRIM.

TRIM syntax	Hasil
TRIM(LEADING “_” FROM “_ABC_”)	“ABC_”
TRIM(TRAILING “_” FROM “_ABC_”)	“_ABC”
TRIM(BOTH “_” FROM “_ABC_”)	“ABC”
TRIM(BOTH “A” FROM “ABC”)	“BC”

Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan. Fungsi TRIM hanya dapat digunakan dengan kolom bertipe karakter atau literal dan tidak dapat digunakan untuk kolom bertipe Memo dan Blob.

6. AVG

Syntax:
AVG([ALL] column_reference | DISTINCT column_reference)

Menghitung rerata dari nilai numerik (nilai kolom, literal, parameter, atau nilai hasil perhitungan). Nilai 0 (nol) dalam suatu kolom tetap diperhitungkan, sedangkan jika tidak ada nilai (kosong bukan nol) maka fungsi AVG akan mengabaikannya. Kombinasi nilai kolom dan suatu konstanta dapat digunakan dalam fungsi AVG.

SELECT
  AVG(ItemsTotal),
  AVG(ItemsTotal * 0.0825) AS AverageTax
FROM Orders

ALL untuk mendapatkan nilai rerata semua record sedang DISTINCT akan mengabaikan nilai yang sama suatu kolom pada saat perhitungan nilai rerata. Jika kata kunci DISTINCT tidak dinyatakan, ALL adalah kata kunci default. Jika memakai klausa GROUP BY, fungsi AVG akan menghitung satu nilai untuk tiap kelompok. Nilai tersebut merupakan pengumpulan kolom untuk semua baris pada tiap kelompok. Pernyataan berikut mengelompokkan nilai rerata kolom ItemsTotal dari tabel Orders, menghasilkan subtotal untuk tiap perusahaan dalam tabel Company.

SELECT
  C."Company",
  AVG(O."ItemsTotal") AS Average
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"

Fungsi AVG hanya beroperasi pad nilai numerik. Untuk nilai non-numerik, sebelum menggunakan fungsi ini, gunakan fungsi CAST untuk mengkonversi kolom menjadi bertipe numerik. Dan fungsi ini tidak dapat digunakan dengan kolom betipe Memo dan Blob.

7. COUNT

Syntax:
COUNT(* | [ALL] column_reference | DISTINCT column_reference)

Untuk menghitung banyaknya record yang ditampilkan oleh pernyataan SELECT (tunggal atau multi query). Jika menambahkan klausa WHERE, fungsi COUNT menampilkan banyaknya record yang tersaring.

SELECT COUNT(*)
FROM "Clients.dbf"
WHERE State = "CA"

8. MAX

Syntax:
MAX([ALL] column_reference | DISTINCT column_reference)

Menghitung nilai terbesar dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Dataset dapat berupa tabel, suatu dataset yang disaring, atau kelompok logika yang dihasilkan oleh klausa GROUP BY. Fungsi MAX memberikan nilai kosong untuk kolom yang bernilai kosong (bukan nol). ALL untuk mendapatkan nilai tertinggi semua baris sedang DISTINCT akan mengabaikan nilai yang sama suatu kolom pada saat menghitung nilai tertinggi. Jika kata kunci DISTINCT tidak dinyatakan, ALL adalah kata kunci default. Kombinasi nilai kolom dan suatu konstanta dapat digunakan dalam fungsi MAX.

SELECT
  MAX(ItemsTotal),
  MAX(ItemsTotal * 0.0825) AS HighestTax
FROM Orders

Jika menggunakan kolom bertipe numerik, nilai yang dihasilkan bertipe sama dengan tipe kolom tersebut (seperti Integer atau Float) . Fungsi MAX tidak dapat digunakan dengan kolom bertipe Memo atau Blob.

SELECT
  C."Company",
  MAX(O."ItemsTotal") AS Biggest
FROM "Customer.db" C, "Orders.db" O
WHERE (C."CustNo" = O."CustNo")
GROUP BY C."Company"
ORDER BY C."Company"

9. MIN

Syntax:
MIN([ALL] column_reference | DISTINCT column_reference)

Fungsi MIN merupakan kebalikan dari fungsi MAX yaitu menghitung nilai terkecil dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Deskripsi lainnya hampir sama dengan fungsi MAX.

SELECT
  MIN(ItemsTotal)
FROM Orders

10. SUM

Syntax:
SUM([ALL] column_reference | DISTINCT column_reference)

Menghitung total dari nilai (nilai kolom, literal, parameter, atau nilai hasil perhitungan) yang menggunakan dataset. Deskripsi lainnya hampir sama dengan fungsi MAX. Contoh penulisan syntax-nya adalah :

SELECT
  SUM(Salary + (Sarary * 0.1))
FROM "Employee.db"

11. CAST

Syntax:
CAST(column_reference AS data_type)

Mengkonversi kolom tertentu dalam tipe data tertentu. CAST dapat diterapkan juga untuk nilai literal dan nilai hasil perhitungan. Fungsi ini dapat digunakan dalam daftar kolom pernyataan SELECT, predikat untuk klausa WHERE dan modifikasi nilai dalam pernyataan UPDATE. Parameter data_tipe yang dapat digunakan adalah Char, Integer, Numeric dan seterusnya, sedangkan yang tidak dapat dipakai adalah Blob, Memo dan Byte. Pernyataan berikut mengkonversi nilai kolom bertipe TimeStamp ke tipe Date

SELECT
  CAST(SaleDate AS DATE)
FROM ORDERS

Kombinasi fungsi CAST dengan fungsi yang lain dapat digunakan selama tipe datanya sesuai, seperti contoh berikut.

SELECT
  SaleDate,
  SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1)
FROM Orders

Perubahan nilai akibat fungsi ini tidak akan disimpan jika diberlakukan dalam pernyataan SELECT. Jika dinyatakan sebagai nilai kondisi (update_atom) dalam pernyataan UPDATE, perubahan nilai tersebut akan disimpan.

12. EXTRACT

Syntax:
EXTRACT(extract_field FROM column_reference)

Untuk mendapatkan kolom tahun, bulan dan hari dari kolom bertipe Date atau TimeStamp. Jika kolom yang dideklarasikan tidak mempunyai nilai maka hasilnya adalah kosong (bukan nol). Jika nilainya tidak kosong, fungsi EXTRACT akan menyatakan nilai elemen tanggal tertentu sebagai data bertipe SmallInt. Parameter extract_fields yang diperbolehkan adalah : YEAR, MONTH, DAY, HOUR, MINUTE, atau SECOND. Parameter YEAR, MONTH, dan DAY hanya digunakan dengan kolom bertipe Date dan TimeStamp. Sedangkan parameter HOUR, MINUTE, dan SECOND hanya digunakan dengan kolom bertipe TimeStamp dan Time.

SELECT
  SaleDate,
  EXTRACT(YEAR FROM saledate) AS YY,
  EXTRACT(MONTH FROM saledate) AS MM,
  EXTRACT(DAY FROM saledate) AS DD
FROM Orders

Pernyataan berikut menggunakan kolom DOB (berisi tanggal lahir) yang menyaring record dimana tanggal lahirnya adalah bulan MEI (dinyatakan dengan angka 5).

SELECT
  DOB,
  LastName,
  FirstName
FROM People
WHERE (EXTRACT(MONTH FROM DOB) = 5)

Fungsi EXTRACT hanya beroperasi pada data bertipe Date, Time dan TimeStamp. Gunakan fungsi CAST untuk mengkonversi tipe data menjadi data bertipe waktu.