Setiap tipe data dalam Oracle memiliki satu grup fungsi tersendiri yang berguna untuk melakukan manupulasi data. Fungsi string digunakan untuk kolom bertipe karakter atau literal, fungsi numerik digunakan untuk kolom bertipe numerik atau literal, fungsi tanggal digunakan untuk kolom bertipe tanggal atau literal. Beberapa fungsi dapat digunakan untuk lebih dari satu tipe data. Pada kesempatan kali ini saya akan memperlihatkan fungsi yang berguna untuk melakukan konversi dari satu tipe data ke tipe data yang lainnya. Daftar di bawah ini adalah beberapa fungsi untuk melakukan konversi tersebut:
- ASCIISTR
Mendapatkan kode ASCII dari suatu string. - BIN_TO_NUM
Melakukan konversi nilai biner ke nilai numeriknya. - DECODE
Melakukan substitusi satu string dengan string yang lain, untuk lebih jelas silahkan lihat penjelasan di bawah. - TO_CHAR
Melakukan konversi NUMBER atau DATE ke string karakter, untuk lebih jelas silahkan lihat di sini. - TO_DATE
Melakukan konversi NUMBER, CHAR, atau VARCHAR2 ke tipe DATE, untuk lebih jelas silahkan lihat di sini. - TO_NUMBER
Melakukan konversi CHAR atau VARCHAR2 ke tipe NUMBER. - TO_TIMESTAMP
Melakukan konversi nilai string ke tipe data TIMESTAMP. - TRANSLATE
Melaukan substitusi karakter demi karakter.
Fungsi konversi yang terpenting
Ada tiga fungsi untuk melakukan konversi yang terpenting yaitu:
- TO_CHAR
- TO_DATE
- TO_NUMBER
Kenapa fungsi ini dibilang penting? TO_DATE biasanya digunakan untuk melakukan perhitungan aritmatik yang melibatkan tanggal. TO_CHAR memungkinkan kita memanipulasi angka seolah-oleh sebagai string (menggunakan fungsi string). TO_NUMBER memungkinkan kita memperlakukan string (yang hanya mengandung angka) seolah-olah sebagai angka.
Misalnya kita ingin menampilkan gaji pegawai dengan menggunakan format uang (1.000.000). Kolom gaji tersimpan dengan tipe NUMBER, sedangkan kita ingin menggunakan fungsi string (SUBSTR dan CONCAT) supaya dapat menampilkan hasil yang diinginkan. Solusinya adalah kita konversi dulu kolom bertipe NUMBER ini ke tipe karakter lalu guanakn fungsi-fungsi string.
SELECT salary AS Tanpa_Format, SUBSTR(TO_CHAR(salary), 1,2)||’.'||SUBSTR(TO_CHAR(salary), -6,3)||’.'||SUBSTR(TO_CHAR(salary), -3) AS Terformat FROM employees
*hasilnya aneh bukan, ini dikarenakan jumlah digit dari data tidak mencapai 8, untuk hasil yang lebih optimat dpat digunakan fungsi-fungsi REGEXP
** di sini saya menggunakan data dari tabel employee, jika anda belum memilikinya silahkan ikuti tutorialnya di sini.
** di sini saya menggunakan data dari tabel employee, jika anda belum memilikinya silahkan ikuti tutorialnya di sini.
Fungsi TO_CHAR akan melakukan konversi kolom salary bertipe NUMBER ke tipe karakter, sehingga dapat dikenakan fungsi SUBSTR. Tapi bagaimana kalau kita langsung menggunakan fungsi SUBSTR kepada kolom bertipe NUMBER tersebut?
SELECT salary AS Tanpa_Format, SUBSTR(salary, 1,2)||’.'||SUBSTR(salary, -6,3)||’.'||SUBSTR(salary, -3) AS Terformat FROM employees
Ternyata hasil yang didapat sama saja. Lalu apakah yang sebenarnya terjadi?
Konversi tipe data otomatis
Ternyata secara otomatis Oracle melakukan konversi terhadap kolom salary tersebut. Oracle akan secara otomatis melakukan konversi berdasarkan fungsi yang dipakai, karena yang dipakai adalah SUBSTR yang merupakan fungsi string, maka kolom salary yang bertipe NUMBER akan dikonversi ke string. Apakah proses konversi ini selalu berhasil? Ternyata tidak, Oracle akan melihat datanya terlebih dahulu, apabila data tersebut terlihat seperti data hasil konversi maka proses konversi akan berjalan. Berikut panduan mengenai proses konversi otomatis ini:
- Setiap tipe data NUMBER dan DATE akan dikonversi ke tipe string. Literal NUMBER tidak harus berada dalam tanda petik (‘), tetapi untuk literal DATE wajib.
- Nilai CHAR atau VARCHAR2 akan dikonversi ke tipe NUMBER jika nilai tersebut hanya mengandung angka, tanda desimal, atau tanda minus di kiri.
- Nilai CHAR atau VARCHAR2 akan dikonversi ke tipe DATE jika nilainya memiliki format default DATE, yaitu DD-MON-YY, jika tidak maka harus dikonversi secara eksplisit, untuk lebih jelas bisa dibaca di sini.
Untuk lebih memahami sifat konversi otomatis silahkan coba beberapa perintah berikut ini:
SELECT INITCAP(LOWER(SYSDATE)) FROM dual
Fungsi INITCAP akan membuat huruf pertama pada kata Dec menjadi huruf besar walaupun kata tersebut berada di tengah-tengah.
SELECT INITCAP(‘it-from_zero+to,hero’) FROM dual
Walaupun kata bukan dipisah dengan spasi, fungsi INITCAP dapat mengenali pembatas kata tersebut (pintas bukan), sebab pembatas kata yang dikenali oleh INITCAP bukan hanya spasi, melainkan semua karakter kecuali huruf.
SELECT SUBSTR(SYSDATE, 4, 3) FROM dual
Tidak perlu lagi melakukan konversi dari DATE ke string (konversi otomatis). Kita juga dapat melakukannya dengan memanfaatkan fungsi EXTRACT untuk mengambil bagian bulannya saja:
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual
Kemudian kita lihat bagaimana konversi otomatis berlangsung untuk tipe NUMBER ke string.
SELECT LPAD(5, 10, 2) FROM dual
SELECT FLOOR(‘-567.58′) FROM dual
Kita lihat bagaimana konversi otomatis dari string ke NUMBER akan berlangsung jika isi string tersebut adalah angka yang valid, silahkan coba jika isi string tersebut bukan angka yang valid.
SELECT SYSDATE, SYSDATE+1, SYSDATE+3 FROM dual
Di dalam panduan mengenai konversi otomatis di atas tidak dijelaskan kalau tipe DATE dapat dikonversi ke tipe NUMBER, tapi mengapa perintah di atas dapat dijalankan? Ingat bahwa untuk beberapa operator dapat bekerja di lebih dari satu tipe data, begitu juga dengan operator + dan -. Di sini operator tersebut bertindak sebagai operator untuk tipe DATE, bukan operator untuk tipe NUMBER.
Fungsi Transformasi
Kenapa disebut fungsi transformasi? Karena fungsi ini akan mengubah nilai inputannya. Ada dua yaitu TRANSLATE dan DECODE. TRANSLATE adalah fungsi sederhana yang akan mengubah tata letak karakter di dalam string, untuk lebih jelas silahkan coba perintah berikut:
SELECT TRANSLATE(7671234, 234567890, ‘BCDEFGHIJ’) FROM dual
Supaya lebih jelas, perintah tersebut saya tuliskan seperti ini:
SELECT TRANSLATE(7671234, 234567890,
‘BCDEFGHIJ’) FROM dual
Dengan adanya fungsi TRANSLATE maka string yang akan ditampilkan akan menyesuaikan dengan urutan parameter pertama 7671234. Ketika TRANSLATE menemukan angka 7, ia akan melihat di parameter kedua (sering disebut bagian if), lalu dia akan mencari karakter yang memiliki posisi yang sama dengan posisi angka 7 di parameter kedua (dapat huruf G), begitu seterusnya. Jika dia tidak menemukan angka yang cocok (angka 1), maka angka inilah yang dikembalikan. Singkat kata, fungsi TRANSLATE akan merubah susunan huruf dari string inputan. Untuk anda yang pernah belajar kriptografi, mungkin hal seperti ini sudah tidak asing lagi.
Fungsi DECODE sama saja seperti fungsi TRANSLATE, bedanya fungsi DECODE akan melakukan substitusi di tingkat nilai (kata). Lihatlah contoh berikut:
SELECT region_id, region_name, DECODE(region_id, 1, ‘Pertamax’, ‘Kesekianx’ ) AS Hasil_decode FROM regions
Di sini kita ingin mendecode region_id, jika region_id bernilai 1 maka disubstitusi dengan ‘Pertamax’, selain itu akan disubstitusi dengan ‘Kesekianx’.
