Fungsi Manipulasi String dalam Oracle 10g

Pada tutorial kali ini saya akan membahas tentang fungsi-fungsi yang biasanya digunakan untuk memanipulasi string. Fungsi di Oracle bekerja dengan dua cara, yang pertama fungsi tersebut menghasilkan nilai baru, kedua fungsi tersebut hanya menampilkan informasi mengenai data yang bersangkutan. Sebelum masuk mengenal fungsi-fungsi tersebut terlebih dahulu sekilas kita mengenal tipe data dalam Oracle.


Beberapa tipe data dalam Oracle adalah NUMBER, CHAR (kependekan dari CHARACTER), DATE, VARCHAR2, LONG, RAW, LONG RAW, BLOB, CLOB, dan BFILE. Kali ini tipe data yang terlibat adalah CHAR dan VARCHAR2.
String sendiri dapat diartikan sebagai kumpulan karakter yang terdiri dari huruf, angka, dan tanda baca (termasuk spasi). Oracle mengenal dua jenis string, yaitu CHAR dan VARCHAR2. Untuk CHAR, panjang dari string adalah tetap, yaitu sepanjang definisi pada saat pembentukan CHAR tersebut. Misalnya kita definsikan data bertipe CHAR dengan panjang 10, maka ORACLE akan ‘memaksakan’ panjang data yang dimasukan menjadi 10 dengan menambahkan spasi di belakangnya. Sedangkan untuk VARCHAR2 panjang data yang dimasukan tidak harus sama dengan panjang saat didefinisikan. Misalnya kita definsikan data bertipe VARCHAR2 dengan panjang 10 sedangkan data yang kita masukan memiliki panjang 5, maka Oracle akan menyimpan data tersebut dengan panjang 5 (tidak ada penambahan).
Berikut ini merupakan fungsi-fungsi yang digunakan dalam manipulasi string:

• ||
  Menggabungkan dua string (operasi concat).
• ASCII
  Mengembalikan nilai ASCII suatu karakter.
• CHR
  Mengembalikan karakter suatu nilai ASCII.
• CONCAT
  Sama fungsinya dengan ||.
• INITCAP
  Mengubah huruf awal tiap kata menjadi huruf besar.
• INSTR
  Mendapatkan posisi karakter dalam suatu string.
• LENGHT
  Mendapatkan panjang suatu string.
• LOWER
  Mengubah string menjadi huruf kecil.
• LPAD
  Memampatkan string sampai panjang tertentu dengan manambahkan karakter di sebelah kiri.
• LTRIM
  Melakukan trim terhadap karakter tertentu di sebelah kiri string.
• NLS_INITCAP
  Melakukan fungsi INITCAP berdasarkan nilai Natural Language Support (NLS).
• NLS_LOWER
  Melakukan fungsi LOWER berdasarkan nilai Natural Language Support (NLS).
• NLS_UPPER
  Melakukan fungsi UPPER berdasarkan nilai Natural Language Support (NLS).
• NLSSORT
  Melakukan fungsi SORT berdasarkan nilai Natural Language Support (NLS).
• REGEXP_INSTR, REGEXP_REPLACE, dan REGEXP_SUBSTR.
• RPAD
  Memampatkan string sampai panjang tertentu dengan manambahkan karakter di sebelah kanan.
• RTRIM
  Melakukan trim terhadap karakter tertentu di sebelah kanan string.
• SOUNDEX
  Mencari kata yang memiliki bunyi sama (mirip operator LIKE).
• SUBSTR
  Mengambil string dari suatu string.
• TREAT
  Mengubah tipe dari suatu ekspresi.
• TRIM
  Melakukan trim terhadap karakter tertentu di sebelah kiri dan kanan string.
• UPPER
  Mengubah string menjadi huruf besar.

Format penggunaan fungsi ini adalah NAMA_FUNGSI(string [, opsi])
Beberapa fungsi memiliki opsi untuk digunakan. Setelah mengetahu fungsi apa saja yang digunakan untuk memanipulasi string sekarang saatnya kita mempraktekan fungsi-fungsi tersebut. Dalam praktek ini saya akan menggunakan tabel DUAL yang merupakan tabel semu dalam ORACLE.
LOWER
Fungsi ini akan mengubah semua karakter dalam string menjadi huruf kecil.

SELECT LOWER(‘ITFROMZEROTOHERO’) AS HASIL FROM dual

CONCATNATION (|| dan CONCAT)
Dua fungsi ini sama kegunaannya, yaitu untuk menggabungkan dua string menjadi satu.

SELECT ‘IT’ || ‘ ‘ || ‘FROM’ || ‘ ‘ || ‘ZERO’ || ‘ ‘ || ‘TO’ || ‘ ‘ || ‘HERO’ AS hasil FROM dual

RPAD
Fungsi ini akan memamptkan string dengan menambahkan karakter di sebelah kanan. Bentuk umumnya adalah RPAD(string, length [, ‘set’]). Length merupakan panjang yanng diinginkan sedangkan ‘set’ adalah karakter yang digunakan untuk menambahkan string tersebut (bisa lebih dari 1 karakter).

SELECT RPAD(‘1234’, 8, ‘*’) FROM dual

LPAD
Sama seperti RPAD, hanya untuk LPAD penambahan karakter dilakukan di sebelah kiri (dapat digunakan untuk mencetak string menjadi rata kanan, defaultnya rata kiri)

SELECT LPAD(‘1234’, 8, ‘ ‘) FROM dual

LTRIM
Fungsi ini akan melakukan trim (pembuangan) karakter di sebelah kiri string. Bentuk umumnya LTRIM(string [, ‘set’]). ‘set’ merupakan kumpulan karakter yang akan ditrim. Cara kerjanya adalah sebagai berikut: pertama Oracle akan mencari mulai dari sebelah kiri string apakah ada karakter yang cocok dengan ‘set’, apabila ada maka karakter tersebut akan dibuang, pencarian berhenti jika Oracle menemukan karakter yang tidak sesuai dengan ‘set’. Perlu diperhatikan proses trim yang terjadi berada pada tingkat karakter, bukan string. Jika kita bermaksud melakukan trim ‘ok’ pada string ‘odol’ maka string yang dihasilkan adalah ‘dol’. Ini disebabkan pada proses pencarian Oracle pertama kali menemukan huruf ‘o’ yang termasuk dalam ‘ok’ pada string ‘odol’, sehingga huruf tersebut dihilangkan, sedangkan pencarian berikutnya Oracle menemukan huruf ‘d’ dalam string ‘dol’ yang tidak ada dalam ‘ok’ sehingga proses berhenti. Jadi ingat, yang ditrim adalah karakter BUKAN kata. Urutan karakter dalam ‘set’ tidak berpengaruh dalam hasil, ‘ok’ dan ‘ko’ akan menghasilkan hal yang sama. Tetapi huruf kecil dan huruf besar berpengaruh, INGAT Oracle akan memperlakukan data secara case sensitif. Sehingga ‘ok’ dan ‘OK’ akan memberikan hasil yang berbeda. Apabila opsi ‘set’ tidak dipakai, yang ditrim adalah spasi.

SELECT LTRIM(‘odol’, ‘ok’) FROM dual
SELECT LTRIM(‘odol’, ‘Ok’) FROM dual

RTRIM
Sama saja cara kerjanya seperti LTRIM hanya RTRIM akan melakukan trim di sebelah kanan string.

SELECT RTRIM(‘itfromzerotohero’, ‘ok’) FROM dual

TRIM
Fungsi ini akan melakukan trim dikiri dan kanan string. Cara kerja sama seperti fungsi LTRIM dan RTRIM. Yang berbeda hanya bentuk umumnya dan karakter ‘set’ yang digunakan HANYA boleh satu.

SELECT TRIM(‘i’ FROM ‘itfromzerotohero’) FROM dual

Fungsi TRIM memiliki dua variasi, yaitu leading (mirip dengan LTRIM) serta trailing (mirip dengan RTRIM).

SELECT TRIM(leading ‘i’ FROM ‘itfromzerotohero’) FROM dual
SELECT TRIM(trailing ‘o’ FROM ‘itfromzerotohero’) FROM dual

LOWER, UPPER, INITCAP
Fungsi LOWER akan mengubah string menjadi huruf kecil. Fungsi UPPER akan mengubah string menjadi huruf besar. Fungsi INITCAP akan mengubah huruf pertama setiap kata menjadi huruf besar.

SELECT LOWER(‘itFromzeroToherO’) FROM dual
SELECT UPPER(‘itfromzerotohero’) FROM dual
SELECT INITCAP(‘it from zero to hero’) FROM dual

LENGTH
Fungsi LENGTH hanya menampilkan informasi berapa panjang string yang dimasukan. Fungsi ini biasanya dijadikan masukan bagi fungsi yang lainnya. Sebagai catatan, kita tidak dapat menggunakan fungsi ini untuk data bertipe LONG.

SELECT LENGTH(‘it from zero to hero’) FROM dual

SUBSTR
Mungkin inilah fungsi yang paling sering digunakan. Fungsi ini berguna jika kita ingin mengambil string dari dalam string. Bentuk umumnya adalah SUBSTR(string , start [, count]). start merupakan posisi awal pemotongan string, sedangkan opsi count akan menentukan berapa banyak karakter yang diambil mulai posisi start. Jika opsi tidak ditulis maka karakter akan diambil mulai dari start hingga karakter terakhir. Apabilai nilai start negatif, maka perhitungan posisi dilakukan mulai dari belakang. Perlu diperhatikan penggunaan start dengan bilangan negatif hanya diperuntukan untuk data bertipe VARCHAR2, JANGAN digunakan untuk data bertipe CHAR. Hal ini disebabkan Oracle selalu menambahkan spasi terhadap data bertipe CHAR jika panjangnya kurang dari panjang yang didefinisikan. Jika start bernilai negatif digunakan oleh SUBSTR dengan data bertipe CHAR maka perhitungannya dilakukan relatif dari akhir string hasil penambahan spasi oleh Oracle, bukan dari string aslinya.

SELECT SUBSTR(‘123456789’ , 3) FROM dual
SELECT SUBSTR(‘123456789’ , 3, 4) FROM dual
SELECT SUBSTR(‘123456789’ , -4) FROM dual
SELECT SUBSTR(‘123456789’ , -4, 2) FROM dual

INSTR
Fungsi ini akan mengembalikan posisi karakter yang dicari dalam suatu string. Sama seperti LENGTH, fungsi ini biasanya digunakan bersama dengan fungsi yang lain. Bentuk umumnya adalah INSTR(string, set [, start [, occurence]]). set merupakan karakter yang dicari posisinya, bisa lebih dari satu karakter. Posisi yang dikembalikan adalah posisi karakter yang pertama kali ditemukan. Misal kita ingin mengetahui posisi ‘el’ dalam string ‘hellohello’, maka yang dihasilkan adalah 2, karena pencarian akan menemukan huruf ‘e’ yang merupakan huruf pertama dari ‘el’ pada string ‘hellohello’ di posisi dua.

SELECT INSTR(‘itfromzerotohero’ , ‘o’) FROM dual

Opsi start digunakan untuk menentukan awal mulai pencarian, defaulnya pada awal string. Sedangkan opsi kedua yaitu occurence digunakan untuk menentukan karakter keberapa yang cocok, defaultnya posisi yang dikembalikan adalah karakter pertama yang cocok.

SELECT INSTR(‘itfromzerotohero’ , ‘o’, 5) FROM dual
SELECT INSTR(‘itfromzerotohero’ , ‘o’, 6) FROM dual
SELECT INSTR(‘hellohello’, ‘el’, 1, 2) FROM dual

ASCII, CHR
Fungsi ASCII akan mengembalikan karakter dari nilai ascii yang dimasukan. Jika yang dimasukan adalah string maka hanya karakter pertama yang akan dihitung. Sedangkan CHR adalah kebalikannya.

SELECT ASCII(‘A’) FROM dual
SELECT ASCII(‘Ada’) FROM dual
SELECT CHR(65) FROM dual

SOUNDEX
Fungsi ini kegunaannya lebih mirip dengan clausa WHERE dengan menggunakan operator LIKE. Sebagai contoh kita akan gunakan untuk menampilkan data pekerjaan yang mirip dengan ‘presiden’. SOUNDEX akan membuat asumsi bagaimana kombinasi huruf dieja dalam bahasa Inggris, dan kedua kata yang dibandingkan harus dimulai dengan huruf yang sama. SOUNDEX dapat dipakai untuk membandingkan data dari dua kolom yang sekiranya memiliki kemiripan bunyi.

SELECT job_title FROM jobs WHERE SOUNDEX(job_title) = SOUNDEX(‘presiden’)

Fungsi-fungsi di atas biasanya kita gunakan dalam melakukan filtering data, yaitu pada clausa WHERE. Contoh-contoh yang saya berikan selalu menggunakan tabel semu dual untuk menyederhanakan tutorial. Penggunaan umumnya data tidak langsung berupa literal string melainkan berupa data yang diambil dari kolom. Untuk itu literal string dapat digantikan dengan nama kolom, tentunya tanpa tanda petik satu. Silahkan bereksperimen dengan fungsi-fungsi manipulasi string ini, sebab tiap fungsi dapat dikombinasikan untuk mendapatkan hasil yang lebih banyak.