Terkadang terdapat suatu kondisi yang membutuhkan pengurutan kode secara manual dalam database MySQL, walaupun sudah terdapat pengkodean urut secara sistem (autoincrement).
Artikel ini menjelaskan cara pembuatan kode urut melalui Query SQL seperti contoh format di bawah ini :
- 001, 002, 003, … 999
- A0001, A0002, … A9999
- AB00001, AB00002, … AB99999
Ketentuan penggunaan Query SQL adalah sebagai berikut :
- Mesti ada data awal dalam suatu tabel sebagai format contoh, misalkan : A001. Selanjutnya Query SQL baru dapat menghasilkan kode selanjutnya sesuai dengan format pengurutan
- Kode awal dapat hanya berupa angka, atau mengandung alphabet di depan (maksimal 3 huruf)
- Jumlah maksimum digit mengikuti data format awal, sebagai contoh A001 mempunyai kode maksimum A999
Query SQL :
SELECT IFNULL(CONCAT(kode,LPAD(angka,lebar_angka,'0')),'') AS kode FROM ( SELECT CONCAT( CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,1,1) ELSE '' END, CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,2,1) ELSE '' END, CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,3,1) ELSE '' END ) AS kode, CONCAT( CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,4)) ELSE CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,3)) ELSE CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,2)) ELSE LENGTH(nama_field_kode) END END END ) AS lebar_angka, CONCAT( CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,4))+1 ELSE CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,3))+1 ELSE CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,2))+1 ELSE MAX(nama_field_kode)+1 END END END ) AS angka FROM nama_tabel GROUP BY 1 LIMIT 1 ) temp;
Penjelasan Sistem Kerja Query SQL :
- Pencarian kode inisial (jika ada), misalkan A pada A001
- Penghitungan jumlah maksimum digit
- Nilai urut sesudah angka terbesar
- Penggabungan kode inisial(poin 1), dengan nilai urut selanjutnya(poin 3) dengan lebar digit sesuai dengan poin 2
Penjelasan parameter :
- nama_tabel : nama tabel
- nama_field_kode : field yang menyimpan kode urut (varchar)
Jika ingin menjadikannya sebagai Store Procedure :
CREATE PROCEDURE sp_getKodeBaru(IN nama_tabel VARCHAR(255), IN nama_field_kode VARCHAR(255), OUT varhasil VARCHAR(255)) BEGIN SET @strQuery = CONCAT('SELECT IFNULL(CONCAT(kode,LPAD(angka,lebar_angka,''0'')),'''') INTO @varhasil FROM ', '( SELECT CONCAT( CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',1,1) ELSE '''' END, CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',2,1) ELSE '''' END, CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',3,1) ELSE '''' END ) AS kode, CONCAT( CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',4)) ELSE CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',3)) ELSE CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',2)) ELSE LENGTH(',nama_field_kode,') END END END ) AS lebar_angka, CONCAT( CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',4))+1 ELSE CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',3))+1 ELSE CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',2))+1 ELSE MAX(',nama_field_kode,')+1 END END END ) AS angka FROM ',nama_tabel,' GROUP BY 1 LIMIT 1 ) tabel_temp'); PREPARE stmt FROM @strQuery; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET varhasil = @varhasil; END
Penjelasan parameter :
- nama_tabel : nama tabel
- nama_field_kode : field yang menyimpan kode urut (varchar)
- varhasil : penampung hasil (kode urut selanjutnya)
Cara pemanggilan Store Procedure di atas :
CALL sp_getKodeBaru('tabel1','kode_urut',@varhasil); SELECT @varhasil AS kode;
Bayu Kandukeswara