Home > Oracle > Fungsi Manipulasi String dalam Oracle

Fungsi Manipulasi String dalam Oracle

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. Untuk info lebih lengkap bisa dibaca di sini. 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.

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

  1. April 27, 2011 at 2:56 am

    Kalo Saya punya String bernilai “AKU CINTA INDONESIA”, terus mau ambil kata CINTA-nya aja harus pake perintah yang mana nih ? Kalo di MYSQL sih gampang, tp di ORACLE masih bingung nih. Thanks

  2. sd
    February 14, 2012 at 2:05 am

    select substr(‘AKU CINTA INDONESIA’,5,5) from dual

  3. May 7, 2012 at 4:41 am

    kalau kita mau ambil setiap suku kata huruf pertama mis ACI dari AKU CINTA INDONESIA bagaimana? tks ya

    • May 7, 2012 at 3:42 pm

      Untuk model seperti ini bisa menggunakan regex. Saya tidak sempat membuat contohnya, tapi sudah ada yang membahas di sini. Weekend ini akan saya buatkan artikelnya dalam bahasa Indonesia.

  4. iyud
    June 11, 2014 at 6:11 am

    gan ane dapat tugas dari dosen nih
    misal kita mau input nama barang nanti otomatis ID barang muncul singkatan dari nama barang tersebut contoh :
    Nama barang yang kita input “Sekolah Menengah Atas” nanti di ID nya otomatis jd SMA
    dan procedure itu berulang. scriptnya gimana yah hehehe thanks,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: