Skip to content

Membuang huruf dari field berisi huruf dan angka (custom MySQL Trim)

14 April 2007

Pada artikel sebelumnya, saya membahas mengenai bagaimana mengurutkan field dengan format huruf dan angka. Jika Anda baca paragraph terakhir artikel tersebut, saya ‘menggelitik’ anda dengan satu pertanyaan bagaimana jika ternyata field yang akan kita urutkan panjang hurufnya tidak fix.
Ada dua cara untuk menyiasati kondisi seperti itu, let’s go green it🙂

1. Carefull Design

Ketika kita ingin membuat sebuah field yang akan berisi kombinasi huruf dan angka, sebaiknya kita usahakan untuk membuat format yang membedakan antara huruf dan angka tsb. Disamping value field akan lebih mudah terbaca, juga manipulasi datanya akan jauh lebih mudah.
Kita bisa memisahkan antara huruf dan angka ke dalam dua field yang berbeda atau juga bisa menggunakan karakter pemisah tertentu seperti ‘-’ atau ‘/’
Sebagai contoh, dari pada kita define valuenya seperti AAFG2345,AB123, maka kita design jadi dua kolom saja menjadi kolom field1 sebagai char dan field2 sebagai integer. Jika nanti kita butuh untuk menggabungkan keduanya kita tinggal menggunakan fungsi CONCAT.

SELECT CONTACT(field1,field2) AS mykode FROM tablex

Hasilnya query diatas akan membuat huruf dan angka (field1 dan field2) tersebut akan menjadi satu field bernama mykode. See, it’s much easier to read right. Nah sekarang untuk mengurutkan fieldnya, Anda tinggal mengurutkannya berdasarkan field1 lalu field2.

Contoh selanjutnya, jika kita menggunakan pemisah antara huruf dan angka, maka hal ini juga akan membuat jauh lebih mudah buat kita. Field value AAFG2345 kita pisahkan menjadi AAFG-2345 dan AB123 menjadi AB-123. Oke, pertanyaannya sekarang bagaimana kita membaca antara huruf dan angkanya ? mengingat menggunakan fungsi substring terasa sulit karena panjang huruf maupun angkanya berbeda.

It’s also easy, jika kita lihat manual MySQL, terdapat perintah substring_index, which means kita akan melakukan substring tapi bukan berdasarkan posisi string, melainkan berdasarkan index atara delimiternya. Well, this helps us a lot. Oke kita liat contohnya, misal kita punya field myfield berisi AAFG-2345 :

SELECT SUBSTRING_INDEX(’AAFG-2345′,’-',1) AS huruf, SUBSTRING_INDEX(’AAFG-2345′,’-',2) AS angka

Setelah di run, query diatas akan menghasilkan field huruf = AAFG dan field angka = 2345. Kewl isn’t it ? Selanjutnya terserah Anda…

2. Create MySQL function

Salah satu feature MySQL versi 5 adalah kita bisa membuat fungsi sendiri sesuai keinginan kita. So solusi kedua jika ternyata kita sudah terlanjur memiliki satu field yang berisi huruf dan angka tanpa delimiter maka kita akan coba bikin satu fungsi sendiri. Logicnya sangat sederhana, kita buang saja semua karakter huruf dari field tsb (trim) sehingga yang tersisa hanyalah karakter angka saja yaitu dengan melakukan looping sejumlah karakter stringnya (LENGTH) dan check apakah karakternya berada dalam range ‘0’ – ‘9’.
Oh ya, untuk fungsi ini kita cukup perlu satu parameter input saja berupa varchar dan outputnya berupa varchar juga namun hanya terdiri dari karakter angka. Untuk menjadikannya pure angka, tentu saja Anda bisa meng-castingnya atau jika tidak tipe data outputnya kita pilih tipe integer sehingga akan otomatis dikonversi oleh MySQL.

Berikut fungsinya, kita beri nama TRIMLETTERS, supaya agak mirip dengan fungsi TRIM bawaan MySQL🙂

CREATE FUNCTION `TRIMLETTERS`(str VARCHAR(20))
RETURNS varchar(20)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Fungsi untuk menghilangkan huruf dari sebuah string'
BEGIN
DECLARE iLoop TINYINT DEFAULT 1;
DECLARE number VARCHAR(20) DEFAULT '';
DECLARE strlen INT DEFAULT CHAR_LENGTH(str);
WHILE iLoop < strlen + 1 DO
IF SUBSTRING(str,iLoop,1) BETWEEN '0' AND '9' THEN
SET number = CONCAT(number,SUBSTRING(str,iLoop,1));
END IF;
SET iLoop = iLoop + 1;
END WHILE;
RETURN number;
END;

Fungsi diatas jika kita run, maka akan disimpan pada database dimana Anda me-run script ini. So, selanjutnya untuk memanggil fungsi diatas cukup dengan TRIMLETTERS(‘STRING ANDA’);

Contoh :
SELECT TRIMLETTERS('AAFG2345') AS mynumber

Query tersebut akan mengembalikan ‘2345’, dan kita lihat semua huruf didalam parameternya sudah hilang.

Oke, guys that’s all for now. Semoga bermanfaat. Sebagai informasi Anda dapat menjadikan fungsi diatas sebagai starting point untuk custom function sesuai keinginan Anda. Misal untuk trim number dari pada letter, dimana Anda tinggal mengganti kondisi IF…BETWEEN..nya atau juga yang lainnya. Gut Laks🙂

3 Comments leave one →
  1. abul permalink
    6 June 2007 3:56 pm

    mas mau nanya nihh, gimana caranya update field umur secara otomatis tiap tahun?thank’s ya mas..

  2. 26 September 2011 10:45 am

    mas, mau nanya ni.., rumus untuk manggabungkan angka dengan huruf gmana ya mas..?

  3. 19 November 2013 5:45 pm

    ANJING LOE!!!!!!!!!!!

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: