Dasar Regular Expression dalam Oracle

Dalam Oracle 10g, fungsi SUBSTR, INSTR, LIKE, dan REPLACE telah ditingkatkan kemampuannya untuk melakukan pencarian menggunakan regular expression. Regular expression mendukung standarisasi kontrol dan pengecekan, misalnya pencocokan nilai lebih dari satu kali, pencarian tanda baca dalam suatu string. Fungs-fungsi baru ini dinamakan REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_LIKE dan REGEXP_REPLACE.

Kita mulai pembahasan dengan menggunakan contoh sederhana, misalnya kita ingin mengambil angka yang berada di posisi tengah dari string ’123-456-7890′ yaitu angka ’456′. Persoalan ini dapat diselesaikan dengan menggunakan kombinasi fungsi SUBSTR dan INSTR, tentunya kita harus mendapatkan dulu posisi tanda ‘-’ yang pertama. Dengan menggunakan fungsi baru kita yaitu REGEXP_SUBSTR kita hanya perlu memberitahu Oracle dimana kita memulai pencarian dan sampai dimana karakter akan diambil.

Pertama kita beritahukan kepada Oracle bahwa kita mencari tanda ‘-’, bentuk regular expressionnya adalah seperti ini:

SELECT REGEXP_SUBSTR(’123-456-7890′, ‘-’

Kemudian kita beritahukan Oracle untuk meneruskan pencarian sampai menemukan tanda ‘-’. Untuk melakukan ini kita gunakan operator ‘[^-' yang dapat diartikan "ambil semua nilai kecuali '-'", sehingga bentuk akhir perintah akan menjadi seperti:

SELECT REGEXP_SUBSTR('123-456-7890', '-[^-]+’) FROM dual

Tanda ‘+’ dimaksudkan untuk mengambil lebih dari satu nilai yang cocok. Apabila kita ingin menambahkan hasil dengan tanda ‘-’ maka bentuk perintahnya:

SELECT REGEXP_SUBSTR(’123-456-7890′, ‘-[^-]+-’) FROM dual

Untuk orang yang baru mulai mempelajari regular expression (termasuk saya) bentuk-bentuk seperti ‘-[^-]+-’ tentu sangatlah sulit dipahami tanpa latihan yang banyak.
Di bawah ini merupakan operator-operator yang dipakai dalam regular expresission, jika anda masih kurang paham silahkan bertanya kepada paman google tentang penggunaan masing-masing operator.

• \
  Karakter backslash dapat memiliki 4 arti: dapat berarti backslash itu sendiri, quote untuk karakter berikutnya, memperkenalkan operator, atau tidak berarti apa-apa.
• *
  Kecocokan 0 atau lebih kemunculan.
• +
  Kecocokan 1 atau lebih kemunculan.
• ?
  Kecocokan 0 atau 1 kemunculan.
• |
  Menyatakan pilihan.
• ^
  Kecocokan dari awal baris suatu karakter.
• $
  Kecocokan dari akhir baris suatu karakter.
• .
  Mencocokan setiap karakter dari himpunan kecuali NULL.
• []
  Menekspresikan daftar kecocokan karakter. Apabila dimulai dengan tanda ^ berarti daftar tersebut merupakan daftar ketidakcocokan
• ()
  Mengelompokan suatu expression.
• {m}
  Kecocokan tepat hanya 1 kali.
• {m,}
  Kecocokan setidaknya 1 kali.
• {m,n}
  Kecocokan setidaknya 1 kali tetapi lebih sedikit dari n.
• \n
  n merupakan bilangan dari 1-9. Mencocokan subexpression ke-n yang diapit oleh tanda ().
• [..]
  Menspesifikaskan collation element.
• [::]
  Menspesifikasikan class karakter, misalnya [:punct:] berarti mencocokan semua tanda baca.
• [==]
  Menspesifikasikan class equivalent.

Cukup bingung setelah membaca penjelasan masing-masing operator di atas? Saya pun demikian  . Tapi tenang saja, sebentar lagi kita akan melihat penggunaan beberapa operator yang biasanya digunakan. Untuk operator yang lain silahkan bertanya kepada paman google  .

REGEXP_SUBSTR

Fungsi ini sama saja kegunaannya dengan SUBSTR, hanya saja ada penambahan regular expression untuk menspesifikasikan awal dan akhir pemotongan string. Nilai yang dikembalikan bertipe VARCHAR2 atau CLOB, bentuk umunya seperti di bawah ini

REGEXP_SUBSTR(string_asal, pattern
[,posisi
[,kemunculan
[,parameter_pencocokan]
]
])

Argumen pattern adalah tempat diletakkannya regular expression, dapat menampung sampai 512 byte. Argumen posisi menentukan dimana harus dimulai proses pencarian dalam string_asal, defaultnya 1 (mulai dari awal). Argumen kemunculan menentukan pada kemunculan keberapa string mulai dipotong. Terakhir argumen parameter_pencocokan digunakan untuk menentukan sifat pencarian, nilai yang boleh diisikan:

• ‘i’ Pencarian bersifat case insensitive.
• ‘c’ Pencarian bersifat case sensitive.
• ‘n’ Menyatakan tanda ‘.’ yang merupakan wildcard diperlakukan sebagai pembatas baris baru.
• ‘m’ Memperlakukan string sebagai string dengan banyak baris.

Apabila variabel parameter_pencocokan ditulis dalam dua nilai, maka nilai terakhir yang akan dipakai, misal ‘ic’, maka pencarian akan dilakukan secara case sensitive. Jika kita memberikan nilai selain nilai-nilai di atas maka Oracle akan mengembalikan error. Apabila parameter_pencocokan tidak diisi, maka efeknya adalah:

• Pencarian bersifat case sensitive.
• Tanda ‘.’ tidak dianggap sebagai pertanda baris baru.
• String diperlakukan sebagai string tunggal.

Perhatikan contoh di bawah ini:

SELECT REGEXP_SUBSTR(‘IT FROM ZERO TO HERO’, ‘TO’, 1, 1, ‘i’) FROM dual

Bandingkan dengan

SELECT REGEXP_SUBSTR(‘IT FROM ZERO TO HERO’, ‘To’, 1, 1, ‘c’) FROM dual

Misalnya pada contoh berikut ini kita ingin mengambil angka ketiga yang terdapat dalam string ’20 itu lebih besar dari 10 loch’. Perhatikan penggunaan class karakter [:digit:].

SELECT REGEXP_SUBSTR(’20 itu lebih besar dari 10 loch’, ‘[[:digit:]]’, 1, 3) FROM dual

Dengan adanya regular expression maka kita tidak perlu lagi repot mencari posisi karakter sebagai awal pemotongan string  .

REGEXP_INSTR

Fungsi REGEXP_INSTR menggunakan regular expression untuk mengembalikan titik permulaan dan akhir dari pattern pencarian. Fungsi ini mengembalikan angka yang merupakan posisi dari awal atau akhir pattern yang dicari atau mengembalikan nol jika tidak ditemukan. Bentuk umumnya:

REGEXP_INSTR(string_asli, pattern
[,posisi
[,kemunculan
[,opsi_pengembalian
[,parameter_pencocokan]
]
]
])

Argumen yang baru di sini adalah opsi_pengembalian. Ada dua nilai yang bisa dimasukan:

• Jika bernilai 0, Oracle akan mengembalikan posisi dari karakter pertama yang cocok, ini defaultnya.
• Jika bernilai 1 Oracle akan mengembalikan posisi setelah karakter pertama yang cocok.

Misalnya kita ingin mencari posisi dari angka kedua yang berada dalam string ‘Umur saya 25 tahun’.

SELECT REGEXP_INSTR(‘Umur saya 25 tahun’, ‘[[:digit:]]’,1, 1, 1) FROM dual

Bandingkan jika argumen opsi_pengembalian bernilai default

SELECT REGEXP_INSTR(‘Umur saya 25 tahun’, ‘[[:digit:]]’) FROM dual

Misalnya kita ingin menampilkan nomor telepon yang memiliki angka 6 lebih dari dua dari tabel employees

SELECT phone_number FROM employees WHERE REGEXP_INSTR(phone_number, ’6′, 1, ’3′) > 0

Untuk pencarian selain angka hati-hati dalam penggunaan argumen parameter_pencocokan.

REGEXP_LIKE

Fungsi ini dapat digunakan sebagai pengganti operator LIKE dalam clausa WHERE. Bentuk umumnya:

REGEXP_LIKE(string_asli, pattern
[,parameter_pencocokan
])

Misalnya kita ingin menampilkan daftar no telp dari tabel employee yang mengandung urutan angka 123.

SELECT phone_number FROM employees WHERE REGEXP_LIKE(phone_number, ’123′)

REPLACE dan REGEXP_REPLACE

Fungsi REPLACE berguna untuk menggantikan satu nilai dalam string dengan nilai yang lain. Bentuk umumnya:

REPLACE(char, string_pencari [,string_pengganti])

Jika string_pengganti tidak diisi, maka jika pencarian menemeui string_pencari string tersebut akan dihilangkan. Inputnya dapat berupa data bertipe CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, atau NCLOB. Contohnya seperti di bawah ini:

SELECT REPLACE(‘IT FROM ZERO TO HERO’, ‘TO’ , ‘to’) FROM dual

Kata ‘TO’ akan diganti dengan ‘to’, contoh di bawah ini akan memperlihatkan jika string_pengganti dihilangkan:

SELECT REPLACE(‘IT FROM ZERO TO HERO’, ‘TO’) FROM dual

Fungsi REGEXP_REPLACE menambahkan kemampuan dari fungsi REPLACE. Dia mendukung penggunaan regular expression untuk menggantikan string_pencarian. Bentuk umumnya:

REGEXP_REPLACE(string_asli, pattern
[,string_pengganti
[,posisi
[,kemunculan
[,parameter_pencarian
]
]
]
])

Jika argumen kemunculan bernilai nol, maka setiap kecocokan string dengan argumen pattern akan dilakukan pergantia dengan string_pengganti. Contoh di bawah ini akan memperlihatkan penggunaan fungsi REGEXP_REPLACE:

SELECT REGEXP_REPLACE(’555-2234′, ’5′, ‘-’, 1, 3) FROM dual
SELECT REGEXP_REPLACE(’021-555-2234′, ‘([[:digit:]]{3})-([[:digit:]]{3})-([[:digit:]]{4})’, ‘(\1) \2-\3′) FROM dual

Fungsi Manipulasi Angka dalam Oracle

Oracle membagi fungsi untuk bekerja dengan angka dalam 3 kategori. Pertama fungsi yang bekerja pada angka tunggal. Kedua fungsi yang bekerja dalam suatu grup angka. Ketiga fungsi yang bekerja pada sederetan angka. Lalu apakah yang dimaksud dengan angka tunggal dalam Oracle, berikut ini termasuk ke dalam angka tunggal:

• Angka biasa seperti 591985.
• Variabel dalam SQL*Plus atau PL/SQL.
• Nilai yang dimiliki kolom tertentu dalam 1 baris.

Biasanya fungsi yang bekerja pada angka tunggal akan menghasilkan nilai baru. Sedangkan yang dimaksud grup angka adalah angka-angka yang terdapat dalam satu kolom yang berasal lebih dari 1 baris (bedakan dengan angka tunggal yang hanya berasal dari satu baris). Misalnya saja semua angka dalam kolom salary dari tabel employees. Fungsi yang bekerja dalam grup angka ini akan menampilkan informasi mengenai grup tersebut, misalnya nilai rata-rata, nilai maksimum. Untuk deretan angka, Oracle mendefinsikannya sebagai berikut:

• Deretan angka bisa seperti 1,2,3,4.
• Variabel dalam SQL*Plus atau PL/SQL.
• Kolom dalam suatu tabel.

Sama seperti fungsi manipulasi string, fungsi angka ini juga terbagi menjadi dua berdasarkan sifatnya, yaitu fungsi yang menghasilkan nilai baru dan fungsi yang memberikan informasi tentang angka tersebut.

Fungsi yang bekerja untuk angka tunggal:

• + Penjumlahan.
• - Pengurangan
• * Perkalian.
• / Pembagian.
• ABS(nilai) Memberikan harga mutlak dari nilai.
• ACOS(nilai) Arkus kosinus dari nilai, dalam radian.
• ASIN(nilai) Arkus sinus dari nilai, dalam radian.
• ATAN(nilai) Arkus tangen dari nilai, dalam radian.
• ATAN2(nilai1, nilai2) Arkus tangen dari nilai1 dan nilai2, dalam radian.
• BITAND(nilai1, nilai2) Melakukan operasi bit AND nilai1 dan nilai2, keduanya harus bernilai positif. Mengembalikan nilai integer.
• CEIL(nilai) Angka bulat terkecil yang lebih besar atau sama dengan nilai.
• COS(nilai) Menghitung nilai cosinus.
• COSH(nilai) Menghitung nilai cosinus hiperbolis.
• EXP(nilai) Bilangan e dipangkatkan dengan nilai.
• FLOOR(nilai) Angka bulat terbesar yang lebih kecil atau sama dengan nilai.
• LN(nilai) Logaritma natural.
• LOG(nilai) Logaritma berbasis 10.
• MOD(nilai, pembagi) Mengembalikan sisa hasil bagi antara nilai dan pembagi.
• NANVL(nilai1, nilai2) Berlaku untuk angka BINARY_FLOAT dan BINARY_DOUBLE. Mengembalikan nilai2 jika nilai1 bukan angka.
• NVL(nilai, pengganti) Mensubsitusi dengan pengganti jika nilai merupakan NULL.
• NVL2(ekspresi1, ekspresi2, ekspresi3) Jika ekspresi1 bernilai NULL, maka ekspresi2 akan menjadi nilai kembalian, jika tidak NULL maka ekspresi3 yang akan menjadi nilai kembalian.
• POWER(nilai, pangkat) Melakukan pemangkatan nilai dengan pangkat.
• REMAINDER(nilai1, nilai2) Mengembalikan sisa hasil bagi antara nilai1 dan nilai2.
• ROUND(nilai, presisi) Melakukan pembulatan terhadap nilai berdasarkan presisi.
• SIGN(nilai) Bernilai 1 jika nilai bertanda positif, -1 jika negatif dan 0 jika nilai = 0.
• SIN(nilai) Menghitung nilai sinus.
• SINH(nilai) Menghitung nilai sinus hiberbolis.
• SQRT(nilai) Menghitung nilai akar pangkat dua dari nilai.
• TAN(nilai) Menghitung nilai tangen.
• TANH(nilai) Menghitung tangen hiperbolis.
• TRUNC(nilai, presisi) Nilai akan dipotong sesuai dengan presisi yang ditetapkan.
• VSIZE(nilai) Besar memory yang digunakan untuk menyimpan nilai dalam Oracle.

Fungsi yang bekerja untuk grup angka:

• AVG(nilai) Menghitung nilai rata-rata dari suatu kolom.
• CORR(nilai1, nilai2) Menghitung nilai koefisien korelasi dari pasangan nilai.
• COUNT(nilai) Menghitung banyaknya angka bukan NULL dalam satu kolom.
• COVAR_POP(nilai1, nilai2) Menghitung nilai kovarian populasi dari pasangan nilai.
• COVAR_SAMP(nilai1, nilai2) Menghitung nilai kovarian sampel dari pasangan nilai.
• CUME_DIST(nilai) Menghitung nilai distribusi kumulatif dari suatu kolom.
• DENSE_RANK(nilai) Menghitung jangkauan dari kolom yang terurut.
• FIRST(nilai) Melakukan fungsi analisis pada baris pertama dalam grup.
• GROUP_ID(nilai) Menentukan grup duplikat hasil dari GROUP_BY.
• GROUPING(ekspresi) Digunakan bersama ROLLUP dan CUBE untuk mendeteksi kehadiran NULL.
• GROUPING_ID Mengembalikan angka yang sesuai dengan vektor bit dari barisnya.
• LAST(nilai) Melakukan fungsi analisis pada baris terakhir dalam grup.
• MAX(nilai) Mencari nilai maksimum dari suatu kolom.
• MEDIAN(nilai) Mencari nilai tengah dari suatu kolom.
• MIN(nilai) Mencari nilai minimum dari suatu kolom.
• PERCENTILE_CONT(nilai) Menghitung nilai percentil, diasumsikan dalam model linear continous.
• PERCENTILE_DISC(nilai) Menghitung nilai percentil, diasumsikan dalam model distribusi diskrit.
• PERCENT_RANK(nilai) Menghitung nilai percentil.
• RANK(nilai) Menghitung rank dari nilai dalam suatu kumpulan nilai.
• REGR Melakukan analisis regresi linear dari kumpulan nilai.
• STATS_BINOMIAL_TEST Melakukan pengujian beda antara proporsi sampel dengan proporsi yang diberikan.
• STATS_CROSSTAB Menganalisa dua nilai nominal.
• STATS_F_TEST Menguji apakah dua nilai memiliki perbedaan yang signifikan.
• STATS_KS_TEST Menguji apakah dua nilai berasal dari populasi yang sama.
• STATS_MODE Mengembalikan nilai yang paling sering muncul dalam grup.
• STATS_MW_TEST Melakukan pengujian dua sampel dengan hipotesis NULL.
• STATS_ONE_WAY_ANOVA Analisis varians satu arah.
• STATS_T_TEST_*fungsi Menghitung perbedaan dari nilai tengah.
• STATS_WSR_TEST Menentukan apakah beda antara sample dan nol adalah signifikan.
• STDDEV(nilai) Menghitung standar deviasi.
• STDDEV_POP(nilai) Menghitung standar deviasi populasi.
• STDDEV_SAMP(nilai) Menghitung standar deviasi sampel.
• SUM(nilai) Menghitung jumlah seluruh angka.
• VAR_POP(nilai) Menghitung nilai varians populasi dari suatu kolom.
• VAR_SAMP(nilai) Menghitung nilai varians sample dari suatu kolom.
• VARIANCE(nilai) Menghitung nilai varians dari suatu kolom.
• WIDTH_BUCKET(ekspresi, min, max, num) Membuat histogram dengan lebar sama.

Fungsi yang bekerja untuk deretan angka:

• COALESCE(nilai1, nilai2,..) Mengembalikan nilai bukan NULL pertama yang ditemui dalam deretan.
• GREATEST(nilai1, nilai2,..) Mengembalikan nilai terbesar dalam deretan.
• LEAST(nilai1, nilai2,..) Mengembalikan nilai terkecil dalam deretan.

Bermain Angka dengan Oracle bagian II (tamat)

Setelah di bagian satu kita melihat fungsi yang bekerja untuk nilai tunggal, sekarang saatnya kita lihat penggunaaan fungsi untuk suatu grup angka. Fungsi yang bekerja dalam suatu grup nilai biasanya disebut dengan fungsi aggregate, biasanya hanya menampilkan informasi mengenai grup tersebut. Beberapa fungsi berguna untuk urusan statistik. Fungsi aggregate tidak akan mengikutsertakan NULL dalam perhitungan. Untuk lebih jelasnya kita langsung saja praktekan, sebelum itu kita perlu menyiapkan tabel sebagai alat bantu. Setelah pada bagian pertama kita membuat tabel mat dan dan diisi data, selanjutnya kita buat tabel temp:

CREATE TABLE temp(
kota VARCHAR2(13) NOT NULL,
tanggal DATE NOT NULL,
siang NUMBER(3,1),
malam NUMBER(3,1)
);

Lalu isikan juga datanya

INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’21-Mar-03′), 62.5, 42.3);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’22-Jun-03′), 51.1, 71.9);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’23-Sep-03′), NULL, 42.3);
INSERT INTO temp VALUES(‘Jakarta’, TO_DATE(’22-Dec-03′), 52.6, 39.8);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’21-Mar-03′), 39.9, -1.2);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’22-Jun-03′), 85.1, 66.7);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’23-Sep-03′), 99.8, 82.6);
INSERT INTO temp VALUES(‘Bandung’, TO_DATE(’22-Dec-03′), -7.2, -1.2);

AVG, COUNT, MAX, MIN, dan SUM

SELECT AVG(siang), COUNT(siang), MAX(siang), MIN(siang), SUM(siang) FROM temp WHERE kota = ‘Jakarta’

Perhatikan gambar 4, untuk baris ke tiga nilai siang adalah NULL. Ini akan mempunyai pengaruh kepada fungsi AVG, karena fungsi ini tidak kebal terhadap ketidakhadiran data. Untuk kota Jakarta ada 3 data yang terisi dalam kolom siang, sementara dalam perhitungan AVG (rata-rata) pembagi yang digunakan adalah total kesuluruhan baris yang dihasilkan dari clasusa WHERE kota = ‘Jakarta’ yaitu 4. Perhatikan dalam kolom COUNT hasil perhitungan, di sana tampil angka 3, bukan 4, sebab NULL di baris ketiga tidak dihitung. MAX akan mencari nilai maksimum dan MIN akan mencari nilai minimum. SUM akan menjumlahkan semua nilai dari kolom siang tanpa mengikutsertakan NULL.

Kita juga dapat mengkombinasikan fungsi nilai tunggal dengan fungsi aggregate. Di sini kita ingin melihat rata-rata perbedaan suhu siang dan malam untuk kota Bandung

SELECT AVG(ABS(siang – malam)) FROM temp WHERE kota = ‘Bandung’

Kenapa kita gunakan fungsi absolut? Sebab hasil dari fungsi pengurangan dapat saja bernilai negatif, sedangkan d sini kita hanya membutuhkan perbedaan nilai antara siang dan malam, tanda negatif tidak kita perhitungkan. Lalu bagaimana jika kita ingin menggabungkan fungsi aggregate dengan fungsi aggregate yang lain, misal

SELECT SUM(AVG(siang)) FROM temp

Kenapa hasilnya error? Ini disebabkan hasil dari AVG adalah nilai tunggal sedangkan SUM adalah fungsi yang bekerja untuk grup.

Sekarang kita lihat contoh berikutnya. Di sini kita ingin menampilkan kota yang memiliki suhu siang tertinggi beserta tanggalnya. Kebanyakan dari kita pasti akan menuliskan query seperti ini

SELECT kota, tanggal, MAX(siang) FROM temp

Dan hasilnya error  . Kenapa bisa error? Dalam query ini pertama kita ingin menampilkan baris dalam kolom kota dan tanggal, sedangkan fungsi MAX(siang) hanya mengembalikan satu nilai. Bertentangan bukan?. Untuk memecahkan permasalahan kita dapat menuliskan subquery seperti ini

SELECT kota, tanggal, siang FROM temp WHERE siang = (SELECT MAX(siang) FROM temp)

STDDEV dan VARIANCE

Bagi yang sudah belajar statistik tentulah familiar dengan istilah Standard Deviasi dan Variance. Berikut ini contoh penggunaannya

SELECT MAX(siang), AVG(siang), MIN(siang), STDDEV(siang), VARIANCE(siang) FROM temp WHERE kota = ‘Jakarta’

Terakhir kita akan melihat penggunaan fungsi yang digunakan dalam deretan angka. Fungsi-fungsi ini akan bekerja pada sekelompok kolom dalam satu baris (beda dengan fungsi aggregate yang bekerja pada satu kolom saja). Dengan kata lain, fungsi-fungsi ini akan membandingkan nilai dari masing-masing kolom dari satu baris lalu mencari nilai tertinggi atau terendah. Sebagai contoh perhatikan hasil perintah berikut

SELECT kota, tanggal, GREATEST(malam, siang) AS tertinggi, LEAST(malam, siang) AS terendah FROM temp

Perhatikan pada baris ketiga, nilainya NULL buka. Ini disebabkan fungsi GREATES dan LEAST tidak kebal terhadap NULL (sama seperti fungsi nilai tunggal). Jika kita lihat hasilnya, fungsi GREATES dan LEAST sama seperti fungsi MAX dan MIN. Hanya saja fungsi MAX dan MIN bekerja dalam 1 kolom.

Fungsi Tanggal dan Waktu dalam Oracle

Berikut ini merupakan daftar fungsi-fungsi yang berkaitan dengan tanggal dan waktu dalam Oracle:

• ADD_MONTHS(date, count)
  Menambahkan bulan ke dalam tanggal.
• CURRENT_DATE Mengembalikan nilai tanggal sekarang berdasarkan time zone.
• CURRENT_TIMESTAMP
  Mengembalikan timestamp sekarang dengan menampilkan informasi time zone.
• DBTIMEZONE
  Mengembalikan time zone database dalam format UTC.
• EXTRACT(timeunit FROM datetime)
  Mengekstarct bagian dari tanggal, seperti mengambil nilai bulannya saja.
• FROM_TZ(timestamp)
  Melakukan konversi nilai timestamp ke nilai timestamp dengan nilai time zone.
• GREATEST(date1, date2, date3,..)
  Mengambil tanggal tertua dalam daftar tanggal.
• LEAST(date1, date2, date3,..)
  Mengambil tanggal termuda dalam daftar tanggal.
• LAST_DAY(date)
  Memberikan tanggal dari hari terakhir dalam bulan yang sama dengan ‘date’.
• LOCALTIMESTAMP
  Mengembalikan timestamp lokal dalam time zone yang aktif tanpa menampilkan informasi time zone.
• MONTHS_BETWEEN(date2, date1)
  Memberikan selisih nilai date2 dan date1 dalam hitungan bulan (dapat bernilai pecahan).
• NEW_TIME(date, ‘this’, ‘other’)
  Memberikan tanggal dan waktu dalam time zone. this akan diganti dengan singkatan tiga huruf dari timezone, other akan diganti dengan singkatan tiga huruf dari timezone lainnya. Time zone tersebut:
• AST/ADT
  Atlantic standard/daylight time
• BST/BDT
  Bering standard/daylight time
• CST/CDT
  Central standard/daylight time
• EST/EDT
  Eastern standard/daylight time
• GMT
  Greenwich mean time
• HST/HDT
  Alaska-Hawai standard/daylight time
• MST/MDT
  Mountain standard/daylight time
• NST
  Newfoundland standard time
• PST/PDT
  Pacific standard/daylight time
• YST/YDT
  Yukon standart/daylight time
• NEXT_DAY(date, ‘day’)
  Memberikan tanggal dari hari yang ditentukan setelah nilai tanggal dalam ‘date’.
• NUMTODSINTERVAL(‘nilai’, ‘dateunit’)
  Melakukan konversi ke nilai bertipe INTERVAL YEAR TO SECOND, dimana dateunit adalah ‘DAY’, ‘HOUR’, ‘MINUTE’, atau ‘SECOND’.
• NUMTOYMINTERVAL(‘nilai’, ‘dateunit’)
  Melakukan konversi ke nilai bertipe INTERVAL YEAR TO MONTH, dimana dateunit adalah ‘DAY’, ‘HOUR’, ‘MINUTE’, atau ‘SECOND’.
• ROUND(date, ‘format’)
  Jika format tidak diberikan maka tanggal akan dibulatkan ke jam 00.00 terdekat.
• SESSIONTIMEZONE
  Mengembalikan nilai dari session time zone.
• SYS_EXTRACT_UTS
  Mengekstract Coordinated Universal Time (UTC) dari tanggal sekarang.
• SYSTIMESTAMP
  Mengembalikan tanggal sistem, termasuk nilai detiknya dan time zone.
• SYSDATE
  Mengembalikan tanggal dan waktu saat statement dieksekusi.
• TO_CHAR(date, ‘format’)
  Memformat ulang tanggal sesuai dengan format yang diberikan.
• TO_DATE(date, ‘format’)
  Melakukan konversi string dengan format yang diberikan ke dalam nilai tanggal. Dapat juga menerima angka, tetapi dengan format yang terbatas.
• TO_DSINTERVAL(‘nilai’)
  Melakukan konversi nilai CHAR, VARCHAR2, NCHAR, atau NVARCHAR2 ke nilai bertipe INTERVAL DAY TO SECOND.
• TO_TIMESTAMP(‘nilai’)
  Melakukan konversi nilai CHAR, VARCHAR2, NCHAR, atau NVARCHAR2 ke nilai bertipe TIMESTAMP.
• TO_TIMESTAMP_TZ(‘nilai’)
  Melakukan konversi nilai CHAR, VARCHAR2, NCHAR, atau NVARCHAR2 ke nilai bertipe TIMESTAMP WITH TIMEZONE.
• TO_YMINTERVAL(‘nilai’)
  Melakukan konversi nilai CHAR, VARCHAR2, NCHAR, atau NVARCHAR2 ke nilai bertipe INTERVAL YEAR TO MONTH.
• TRUNC(date, ‘format’)
  Jika format tidak dituliskan maka proses truncate akan memotong tanggal sampai jam 00.00.
• TZ_OFFSET(‘nilai’)
  Mengembalikan offset dari time zone sesuai dengan nilai yang dimasukan berdasarkan tanggal statement tersebut dieksekusi.

Bermain Angka dengan Oracle bagian I

Sesuai dengan judulnya, sekarang kita akan bermain-main dengan angka dalam Oracle. Pada tutorial ini kita akan melihat bagaimana penggunaan fungsi-fungsi untuk manipulasi angka, tentunya hanya fungsi-fungsi yang paling umum dipakai saja yang akan saya jelaskan penggunaannya. Sebelum kita mulai terlebih dahulu kita persiapkan data yang akan digunakan selama tutorial ini, ada dua tabel yang akan kita pakai, tabel mat dan tabel temp. 

Pertama kita akan melihat penggunaan fungsi untuk nilai tunggal, untuk itu kita membutuhkan data dari tabel mat sebagai contoh. Dari jendela SQL*Plus kita ketikan perintah untuk membuat tabel mat:

CREATE TABLE mat(
name varchar2(20) NOT NULL,
above number(8,3) NOT NULL,
below number(8,3) NOT NULL,
empty number(8,3)
);

Lalu kita isikan datanya

INSERT INTO mat(name, above, below) VALUES(‘whole number’, 11, -22);
INSERT INTO mat(name, above, below) VALUES(‘low decimal’, 33.33, -44.44);
INSERT INTO mat(name, above, below) VALUES(‘mid decimal’, 55.5, -55.5);
INSERT INTO mat(name, above, below) VALUES(‘high decimal’, 66.666, -77.777);

Dalam Oracle, kolom bertipe number dapat tidak memiliki nilai. Ketika dia dinyatakan sebagai NULL, maka itu berarti datanya tidak ada (bukan bernilai nol). Pertama kita akan melihat penggunaan fungsi matematika dasar (penjumlahan, pengurangan, perkalian, pembagian). Ketikan perintah berikut dan perhatikan hasilnya

SELECT name, above, below, empty, above + below AS tambah, above – below AS kurang, above * below AS kali, above / below AS bagi FROM mat

Kemudian fungsi kita terapkan lagi tetapi dengan kolom yang bernilai NULL, yaitu empty, silahkan dicoba

SELECT name, above, below, empty, above + empty AS tambah, above – empty AS kurang, above * empty AS kali, above / empty AS bagi FROM mat

Tidak ada hasil yang ditampilkan bukan. Ini karena NULL tidak dapat diikut sertakan dalam perhitungan, semua operasi perhitungan dengan NULL akan bernilai NULL.

NVL(NULL VaLue substitusion)

Di atas saya katakan bahwa NULL merepresentasikan ketidak hadiran data. Lalu bagaimana kita dapat bekerja dengan NULL ini. Salah satu fungsi yang bekerja dengan NULL adalah NVL. Gunanya adalah menggantikan NULL dengan nilai tertentu. Misal dalam suatu tabel muncul NULL dan kita ingin menggantikan NULL ini dengan suatu angka. Bentuk umumnya adalah

NVL(nilai, pengganti)

Argumen nilai ini kita ganti dengan nama kolom dimana NULL muncul, lalu pengganti adalah nilai yang akan menggantikan NULL. Setiap NULL yang muncul dalam kolom tersebut akan diganti dengan pengganti.

NVL2

Mirip seperti NVL, NVL2 juga bekerja dengan NULL. Bentuk umumnya

NVL2(ekspresi1, ekspresi2, ekspresi3)

ekspresi1 adalah ekepresi yang dinilai apakah NULL atau tidak, jika NULL maka ekspresi2 yang dikembalikan, jika tidak maka ekspresi3 yang dikembalikan. Mirip dengan operator ternary dalam Java  .

ABS (ABSolute)

Fungsi ini akan memberikan nilai posotif, dengan kata lain dia akan membuat bilangan negatif menjadi positif

SELECT ABS(-22.5) AS hasil FROM dual

CEIL

Fungsi ini akan mengembalikan bilangan bulat terkecil yang lebih besar dari nilai yang ditentukan. Perhatikan perbedaan hasilnya jika bilangan yang dimasukan adalah negatif.

SELECT CEIL(2.4) FROM dual
SELECT CEIL(-2.4) FROM dual

FLOOR

Fungsi ini akan mengembalikan bilangan bulat terbesar yang lebih kecil dari nilai yang ditentukan. Perhatikan perbedaan hasilnya jika bilangan yang dimasukan adalah negatif.

SELECT FLOOR(2.4) FROM dual
SELECT FLOOR(-2.4) FROM dual

MOD dan REMAINDER

Kedua fungsi ini sama kegunaannya, yaitu untuk mencari sisa hasil bagi dari kedua bilangan. Bentuk umumnya

MOD(nilai_yang_dibagi, pembagi)
REMAINDER(nilai_yang_dibagi, pembagi)

MOD akan bernilai nol jika bilangan yang dibagi adalah negatif atau nol. MOD juga bernilai nol jika bilangan pembagi adalah 1.

SELECT MOD(15, 4) FROM dual
SELECT REMAINDER(15, 4) FROM dual
SELECT MOD(15, 0) FROM dual
SELECT MOD(15, -4) FROM dual
SELECT MOD(4.1, 0.3) FROM dual
SELECT MOD(-15, 3) FROM dual
SELECT MOD(5, 1) FROM dual

POWER

Fungsi ini digunakan untuk memangkatkan bilangan yang satu dengan bilangan kedua. Bentuk umumnya

POWER(nilai1, nilai2)

nilai2 dapat berasal dari bilangan real apa saja.

SELECT POWER(2, 3) FROM dual
SELECT POWER(2, 3.3) FROM dual
SELECT POWER(2, -3) FROM dual
SELECT POWER(-2, 3) FROM dual

SQRT (SQRuare Root)

Fungsi ini akan menghitung nilai akar pangkat 2 dari suatu bilangan. Perlu diperhatikan, Oracle tidak mendukung bilangan imajiner. Oleh karena itu bilangan yang dijadikan sebagai parameter dalam fungsi ini haruslah positif.

SELECT SQRT(64) FROM dual
SELECT SQRT(66.666) FROM dual

EXP, LN, LOG

Untuk urusan bisnis fungsi-fungsi ini jarang sekali digunakan, tapi di dunia sains, fungsi ini memegang peranan penting. EXP adalah fungsi yang akan memangkatkan bilangan e (2.71818283..) dengan bilangan tertentu. LN adalah fungsi yang akan menghitung logaritma dengan basis bilangan e (logaritma natural). LOG adalah fungsi untuk menghitung logaritma dengan basis yang ditentukan. Bentuk umumnya

EXP(nilai)
LN(nilai)
LOG(basis, nilai)

Supaya lebih jelas langsung saja praktekan perintah-perintah berikut

SELECT EXP(2) FROM dual
SELECT LN( 7.3890561) FROM dual
SELECT LOG(10, 1000) FROM dual

ROUND dan TRUNC

Dua fungsi ini gunanya sama hanya berbeda cara kerja. Kegunaannya adalah memotong angka sesuai presisi yang diinginkan. Bentuk umumnya

ROUND(nilai, presisi)
TRUNC(nilai, presisi)

Supaya lebih jelas langsung saja praktekan perintah berikut

SELECT name, above, below, ROUND(above, 2) AS rnd1, ROUND(below, 2) AS rnd2, TRUNC(above, 2) AS trnc1, TRUNC(below, 2) AS trnc2 FROM mat

Fungsi ROUND dan TRUNC yang kita gunakan di atas memakai presisi 2, artinya angka akan dipotong sampai menjadi 2 angka dibelakang koma. Tapi lihat perbedaan hasil yang diberikan pada baris terakhir. Kita perhatikan untuk fungsi ROUND angka 66.666 dan -77.777 akan dipotong dengan mengalami pemotongan dan pembulatan menjadi 66.67 dan -77.78. Bilangan desimal di atas atau sama dengan 5 akan dibulatkan ke atas, jika di bawah akan dihilangkan (0.66 menjadi 0.67, 0.77 menjadi 0.78). Sedangkan untuk hasil TRUNC hanya mengalami pemotongan.

Jika presisi bernilai nol, maka artinya bilangan desimalnya akan dihilangkan. Untuk lebih jelas silahkan perhatikan hasil dari perintah berikut

SELECT name, above, below, ROUND(above, 0) AS rnd1, ROUND(below, 0) AS rnd2, TRUNC(above, 0) AS trnc1, TRUNC(below, 0) AS trnc2 FROM mat

Perhatikan pembulatan yang terjadi (55.5 menjadi 56, 33.33 menjadi 33, 66.666 menjadi 67)

Dapat juga dipakai bila nilai presisi negatif

SELECT name, above, below, ROUND(above, -1) AS rnd1, ROUND(below, -1) AS rnd2, TRUNC(above, -1) AS trnc1, TRUNC(below, -1) AS trnc2 FROM mat

SIGN

Fungsi ini gunanya untuk mengetahui tanda dari suatu bilangan. Perhatikan contoh

SELECT SIGN(25) FROM dual
SELECT SIGN(-25) FROM dual
SELECT SIGN(0) FROM dual

SIN, SINH, COS, COSH, TAN, TANH, ACOS, ATAN ATAN2 dan ASIN

Semua fungsi ini adalah fungsi yang berhubungan dengan trigonometri. Untuk SIN, COS dan TAN nilai yang digunakan sebagai parameter adalah nilai derajat dalam radian (pi/180)

SELECT SIN(45*3.141592655/180) FROM dual
SELECT COS(45*3.141592655/180) FROM dual
SELECT TAN(45*3.141592655/180) FROM dual

Untuk penjelasan mengenai fungsi-fungsi yang digunakan dalam grup angka silahkan lihat bagian dua

Bermain Tanggal dengan Oracle

DATE adalah salah satu tipe dalam dalam Oracle, seperti halnya VARCHAr2 dan NUMBER. Tipe data DATE disimpan oleh Oracle dalam format spesial yang menyimpan tidak hanya bulan, tahun dan tanggal tetapi juga menyimpan jam, menit dan detik. Kita dapat memformat tampilan data bertipe DATE ini sehingga dapat menampilkan tanggal saja atau tanggal dengan jam, atau abad. Kita dapat menggunakan tipe data TIMESTAMP untuk menyimpan bilangan detiknya. SQL*Plus dan SQL mengenali kolom yang bertipe DATE, dan mereka memahami instruksi untuk melakukan operasi aritmatik terhadap data tersebut.

SYSDATE, CURRENT_DATE, SYSTIMESTAMP

Oracle akan mengambil nilai tanggal dan jam di komputer Orcle tersebut terinstal sebagai nilai current date and time. Kita dapat mengambilnya melalui fungsi SYSDATE (SYStem DATE). Fungsi kedua yaitu CURRENT_DATE, akan mengambil nilai tanggal dan waktu berdasarkan time zone tempat komputer Oracle terinstal. Fungsi ketiga, SYSTIMESTAMP, akan mengambil nilai tanggal dan waktu adri komputer tempat Oracle terinstal tetapi ditampilkan dalam format TIMESTAMP.

SELECT SYSDATE FROM dual
SELECT CURRENT_DATE FROM dual
SELECT SYSTIMESTAMP FROM dual

Menghitung perbedaan antara dua tanggal

Seperti saya jelaskan di awal, Oracle dapat melakukan perhitungan aritmatik terhadap data bertipe DATE. Contoh berikut akan memperlihatkan salah satu penggunaan operasi aritmatik, yaitu pengurangan, kita akan mencoba untuk mencari tahu perbedaan tanggal antara nilai dari kolom hire_date dalam kolom employees dengan tanggal sekarang, ketikan perintah berikut:

SELECT hire_date AS Tanggal_Masuk, SYSDATE AS Tanggal_Sekarang, SYSDATE-hire_date AS Beda_Tanggal FROM employees

Jika anda belum memiliki tabel employees, anda dapat mengikuti tutorialnya di sini.

Menambahkan bulan

Misalnya kita ingin mencari tahu tanggal berapa setelah 4 bulan dari sekarang, perintahnya adalah sebagai berikut:

SELECT ADD_MONTHS(SYSDATE, 4) AS Empat_bulan_kemudian FROM dual

Atau misalnya kita ingin melakukan evaluasi terhadap karyawan kita (dari tabel employees), evaluasi ini dilakukan 10 bulan setelah mereka masuk kerja (dari kolom hire_date), maka perintahnya adalah

SELECT hire_date AS Tanggal_masuk, ADD_MONTHS(hire_date, 10) AS Tanggal_evaluasi FROM employees

Mengurangkan bulan

Sama-sama menggunakan fungsi ADD_MONTHS, tetapi dengan memasukan parameter negatif. Misal kita ingin tahu tanggal dari 5 bulan sebelum tanggal sekarang.

SELECT ADD_MONTHS(SYSDATE, -5) FROM dual

Atau misalnya kita ingin melakukan liburan pada tanggal 10 September 2011, pemesanan tempat paling lambat dilakukan 3 bulan sebelum hari H, tanggal berapa kita harus sudah memesan tempat tersebut?

SELECT ADD_MONTHS(TO_DATE(’10-Sep-11′), -3)-1 AS Tanggal_pesan FROM dual

Jawabannya adalah kita paling lambat harus memesan tempat pada tanggal 9 Juni 2011.

GREATES dan LEAST

Masih ingat pembahasan fungsi ini pada tutorial Bermain angka dengan Oracle, di sini fungsinya sama saja, hanya di sini kita terapkan pada data bertipe tanggal. GREATES akan mengembalikan tanggal yang tertua sedangkan LEAST akan mengembalikan tanggal yang termuda.

SELECT GREATEST(TO_DATE(’10-Sep-12′),TO_DATE(’10-Oct-12′)) FROM dual
SELECT LEAST(TO_DATE(’10-Sep-12′),TO_DATE(’10-Oct-12′)) FROM dual

Kalau anda perhatikan, beberapa kali saya menggunakan fungsi TO_DATE di atas, mengapa saya harus menggunakan fungsi ini? Jawabannya adalah karena saya mengoperasikan fungsi-fungsi tanggal ini ke dalam nilai literal, sehingga kita harus terlebih dahulu mengkonversi literal ini ke dalam format tanggal supaya sesuai. Jika data yang kita operasikan berasal dari kolom bertipe DATE, maka konversi dengan TO_DATE tidak kita perlukan (perhatikan contoh pertama penggunaan fungsi ADD_MONTHS). Untuk lebih jelas silahkan coba perintah berikut dan bandingkan hasilnya dengan contoh sebelumnya

SELECT GREATEST(’10-Sep-12′,’10-Oct-12′) FROM dual
SELECT LEAST(’10-Sep-12′,’10-Oct-12′) FROM dual

Bentuk umum fungsi TO_DATE:

TO_DATE(string [,'format'])

Dengan ketidakhadiran fungsi TO_DATE, maka tanggal yang dimasukan akan dianggap sebagai string dan fungsi GREATEST dan LEAST akan memperlakukan tanggal tersebut sebagai string. Beberapa batasan yang dilakukan dalam fungsi TO_DATE:

• Literal tidak boleh berbentuk string, misalnya “saya ganteng”.
• Literal tidak boleh berbentuk ejaan, misalnya “Friday”, harus berbentuk angka.
• Tanda baca diijinkan.
• Format fm tidak diperlukan, jika ada maka akan diabaikan.
• Jika literal mengandung bulan, maka penulisannya harus merupakan ejaan bulan tersebut, misal “sep” jika memakai MON atau “september” jika memakai MONTH

Silahkan coba contoh-contoh berikut supaya lebih memahami:

SELECT TO_DATE(’20-Sep-1988′, ‘DD-MON-YY’) FROM dual
SELECT TO_DATE(’20091988′, ‘DDMMYYYY’) FROM dual

Coba perhatikan contoh berikut:

SELECT TO_DATE(’09-20-88′) FROM dual

Yang tampil adalah error, sebab Oracle tidak mengenali format penulisan tanggal seperti bulan-hari-tahun. Untuk membuatnya dikenali maka kita harus memberitahunya secara eksplisit seperti di bawah ini:

SELECT TO_DATE(’09-20-88′, ‘MM-DD-YY’) FROM dual

NEXT_DAY

Misalnya kita ingin mencari tahu tanggal berapakah hari kamis pertama setelah tanggal 9 Desember 2010, perintahnya sebagai berikut:

SELECT NEXT_DAY(TO_DATE(’09-Dec-10′), ‘Thuesday’) AS Kamis FROM dual

Fungsi NEXT_DAY sama seperti fungsi lebih besar dari (>), dia akan mencari tanggal dari hari yang lebih besar dari tanggal yang ditetapkan.

LAST_DAY

Fungsi ini akan mengembalikan tanggal terakhir dalam bulan yang bersangkutan.

SELECT LAST_DAY(SYSDATE) FROM dual

Mencari perbedaan bulan antara dua tanggal

Misal kita ingin mencari tahu berapa bulan lamanya suatu karyawan bekerja, dihitung dari tanggal hire_date dan tanggal sekarang

SELECT first_name AS Nama, hire_date, MONTHS_BETWEEN(SYSDATE,hire_date) AS Lama_kerja FROM employees

Hasilnya tidak bagus bukan, masih mengandung pecahan. Untuk menghilangkannya kita gunakan saja fungs FLOOR.

SELECT first_name AS Nama, hire_date, FLOOR(MONTHS_BETWEEN(SYSDATE,hire_date)) AS Lama_kerja FROM employees

Kombinasi antara beberapa fungsi

Misalnya kita ingin menaikan gaji kerja karywan, kenaikan gaji baru kita lakukan setelah 6 bulan bekerja, tanggal berapakah gaji karyawan tersebut sudah naik?

SELECT first_name AS Nama, hire_date AS Tanggal_masuk, LAST_DAY(ADD_MONtHS(hire_date, 6))+1 AS Gaji_naik FROM employees

Pertama kita memakai fungsi ADD_MONTHS untuk mencari tahu tanggal setalah 6 bulan, kemudian kita ,menggunakan fungsi LAS_DAY untuk mencari tahu tanggal terakhir di bulan itu, setelah dapat tanggal tersebut ditambahkan 1 untuk mendapatkan tanggal 1 bulan berikutnya.

Jika kita ingin mencari tahu seberapa lama para karyawan harus bekerja sebelum mengalami kenaikan gaji, kita dapat melakukannya dengan menggunakan perintah berikut:

SELECT first_name AS Nama, hire_date AS Tanggal_masuk, (LAST_DAY(ADD_MONtHS(hire_date, 6))+1)-hire_date AS Tunggu FROM employees

Penggunaan ROUND dan TRUNC

Di awal kita sudah melihat bahwa data bertipe tanggal dapat dikenai operasi aritmatik (dicontohkan operasi pengurangan). Tapi kita perhatikan hasilnya mempunyai bilangan pecahan, apa yang terjadi? Ini disebabkan Orcale menyimpan tanggal berikut dengan jam, menit dan detik, sehingga nilai-nilai ini turut diperhitungkan. Untuk mengatasinya kita harus melakukan pembulatan terhadap data tanggal tersebut sebelum dikenai operasi aritmatik. Beberapa asumsi mengenai pembulatan yang dilakukan:

• Tanggal yang dimasukan sebagai literal, contoh ’10-Sep-2010′ diberikan nilai jamnya adalah 00.00 (awal hari).
• Tanggal yang dimasukan melalui SQL*Plus, tanpa diberitahukan secara spesifik formatnya, akan dianggap memiliki nilai jam 00.0.
• SYSDATE akan selalu memiliki komponen tanggal dan waktu. Pembulatan (ROUND) akan dilakukan ke jam 00.00 terdekat. Jika waktu bernilai sebelum 12.00 akan dibulatkan ke jam 00.00, jika sesudah 12.00 akan dibulatkan ke jam 24.00 (00.00 hari berikutnya). Kalau TRUNC akan selalu menset waktu ke jam 00.00 hari yang bersangkutan.

SELECT TO_DATE(’08-Dec-10′)-ROUND(SYSDATE) FROM dual

*perintah ini saya jalankan pada tanggal 9 Desember 2010 jam 20:27

TO_DATE dan TO_CHAR

Fungsi TO_DATE sudah saya bahas sedikit di atas, fungsi TO_CHAR berfungsi kebalikannya, yaitu mengubah tanggal menjadi bertipe string. Bentuk umumnya:

TO_DATE(string [,'format'[,'NLSparameter']])
TO_CHAR(date [,'format'[,'NLSparameter']])

Untuk ‘date’, harus berasal dari kolom yang bertipe date, jika ingin digunakan literal maka harus dibungkus dengan fungsi TO_DATE. Sedangkan string dapat berasal dari kolom yang mengandung string atau angka, literal string atau literal angka. ‘format’ adalah format tanggal, ada banyak sekali format tanggal dalam Oracle, di bawah ini hanya sebagian format yang paling sering digunakan dalam fungsi TO_CHAR dan TO_DATE:

• / , – : . ;
  Tanda baca yang akan ditampilkan pada fungsi TO_CHAR, untuk TO_DATE akan diabaikan.
• A.D atau AD
  Indikator AD, dengan atau tanpa tanda titik.
• A.M atau AM
  Menampilkan AM atau PM, tergantung nilai waktunya, dengan atau tanpa tanda titik.
• B.C atau BC
  Sama seperti A.D atau AD.
• CC
  Nilai abad, misalnya 21 untuk tahun 2010.
• D
  Angka hari dalam seminggu, bernilai 1-7.
• DAY
  Nama hari, dalam bahasa Inggris.
• DD
  Angka hari dalam 1 bukan, bernilai 1-31.
• DDD
  Angka hari dalam setahun, dihitung sejak 1 Januari, bernilai 1-366.
• DL
  Tanggal dalam format panjang, untuk standar Amerika berformat ‘fmDay, Month dd, yyyy’.
• DS
  Tanggal dalam format pendek, untuk standar Amerika berformat ‘MM/DD/RRRR’.
• DY
  Nama hari disingkat dalam tiga huruf, misal FRI untuk Friday.
• FM
  Menghilangkan spasi di akhir dan awal sehingga tanggal dan waktu ditampilkan hanya selebar datanya.
• HH
  Jam dalam satu hari, bernilai 1-12.
• MM
  Angka bulan dalam satu tahun, bernilai 1-12.
• MON
  Nama bulan disingkat menjadi tiga huruf,misal Sep untuk September.
• MONTH
  Nama bulan, dalam bahasa Inggris.
• P.M
  Sama seperti A.M.
• YEAR
  Sebutan untuk tahun.
• YYYY
  Tahun dalam bentuk 4 digit
• Y,YYY
  Tahun dengan pemisah koma untuk digit pertama.
• Y
  Digit terakhir dari tahun.
• YY
  Dua digit terakhir dari tahun.
• YYY
  Tiga digit terakhir dari tahun.

Format berikut hanya berfungsi untuk TO_CHAR

• TH
  Akhiran untuk angka, misal ddTH akan menghasilkan 24th. Besar kecilnya huruf tergantung dari penulisan format tanggalnya.
• SP
  Akhiran untuk angka yang memaksa angka tersebut dituliskan bunyinya, misal DDSP dapat menghasilkan Three. Besar kecilnya huruf tergantung dari penulisan format tanggalnya.
• SPTH
  Kombinasi dari SP dan TH.
• THSP
  Sama seperti SPTH.

Perhatikan contoh-contoh penggunaannya:

SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DD Month YEAR’) AS Akhir FROM employees

SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DD-MM-YYYY’) AS Akhir FROM employees

SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DDspth MONTH YYYY’) AS Akhir FROM employees

SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘fmDDth MONTH YYYY’) AS Akhir FROM employees

SELECT first_name AS Nama, hire_date AS Tanggal_masuk, TO_CHAR(hire_date, ‘”Masuk pada tanggal” DD fmMONTH YYYY’) AS Akhir FROM employees

SELECT first_name AS Nama, hire_date AS Tanggal_masuk, TO_CHAR(hire_date, ‘”Masuk pada tanggal” DD fmMONTH YYYY “pada jam” HH:MI P.M.’) AS Akhir FROM employees