Mengurutkan Berdasarkan Panjang Substring Berubah - MySQL Dasar #44

Selanjutnya disini Anda akan belajar mengurutkan bagian - bagian sebuah kolom yang tidak ada pada posisi tertentu di dalam kolom tersebut.

Baca sebelumnya : Mengurutkan Berdasarkan Panjang Substring - MySQL Dasar #43

Daftar Isi : Pengurutan Hasil Query

Jika misalnya substring - substring yang ingin Anda gunakan untuk pengurutan memiliki panjang yang tak tetap atau berubah - ubah, Anda perlu menggunakan cara yang bisa diandalkan dalam mengekstrak bagian kolom yang Anda inginkan. Untuk melihat bagaimana hal ini bisa dilakukan, Anda perlu menciptakan sebuah tabel baru misalnya disini adalah tabel rumah2 seperti tabel rumah pada pembelajaran sebelumnya hanya saja disini pada nomor serialnya tidak terdapat awalan 0 dari nilai id.

MariaDB [root93]> CREATE table rumah2(id VARCHAR(30), deskripsi VARCHAR(50));
Query OK, 0 rows affected (0.23 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('RMM40672ID','meja makan');
Query OK, 1 row affected (0.05 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('DPR372UK','oven microwave');
Query OK, 1 row affected (0.05 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('DPR1729JP','kulkas');
Query OK, 1 row affected (0.05 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('TDR38SG','lampu tidur');
Query OK, 1 row affected (0.09 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('MND485US','shower');
Query OK, 1 row affected (0.05 sec)

MariaDB [root93]> INSERT INTO rumah2(id, deskripsi) VALUES('MND415ID','bath tube');
Query OK, 1 row affected (0.08 sec)

MariaDB [root93]> SELECT * FROM rumah2;
+------------+----------------+
| id         | deskripsi      |
+------------+----------------+
| RMM40672ID | meja makan     |
| DPR372UK   | oven microwave |
| DPR1729JP  | kulkas         |
| TDR38SG    | lampu tidur    |
| MND485US   | shower         |
| MND415ID   | bath tube      |
+------------+----------------+
6 rows in set (0.00 sec)

Bagian kategori dan bagian negara dari nilai id dapat diekstrak dan diurutkan menggunakan fungsi seperti LEFT dan RIGHT, sama seperti tabel rumah pada pembelajaran sebelumnya.

Tetapi sekarang segmen dari id pada tabel rumah2 memiliki panjang yang tidak tetap dan tidak dapat diekstrak dan diurutkan menggunakan pemanggilan sederhana.

Untuk itu Anda perlu menggunakan fungsi SUBSTRING untuk melompati tiga karakter pertama dan menghasilkan sisa karakter lain yang dimulai dari karakter keempat.

MariaDB [root93]> SELECT id, SUBSTRING(id,4), deskripsi FROM rumah2;
+------------+-----------------+----------------+
| id         | SUBSTRING(id,4) | deskripsi      |
+------------+-----------------+----------------+
| RMM40672ID | 40672ID         | meja makan     |
| DPR372UK   | 372UK           | oven microwave |
| DPR1729JP  | 1729JP          | kulkas         |
| TDR38SG    | 38SG            | lampu tidur    |
| MND485US   | 485US           | shower         |
| MND415ID   | 415ID           | bath tube      |
+------------+-----------------+----------------+

Selanjutnya Anda bisa mengabaikan 2 karakter dari hasil substring dengan cara menguranginya seperti berikut

MariaDB [root93]> SELECT id, LEFT(SUBSTRING(id,4), LENGTH(SUBSTRING(id,4)-2)) FROM rumah2;
+------------+--------------------------------------------------+
| id         | LEFT(SUBSTRING(id,4), LENGTH(SUBSTRING(id,4)-2)) |
+------------+--------------------------------------------------+
| RMM40672ID | 40672                                            |
| DPR372UK   | 372                                              |
| DPR1729JP  | 1729                                             |
| TDR38SG    | 38                                               |
| MND485US   | 485                                              |
| MND415ID   | 415                                              |
+------------+--------------------------------------------------+
6 rows in set, 6 warnings (0.00 sec)

Bila diejalskan secara detail dari query diatas adalah:

Fungsi Left dan substring dikombinasikan, dimana LEFT mengambil sisa karakter dari kiri berdasarkan jumlah karakter/string yang dihitung oleh LENGTH
LEFT(SUBSTRING(id,4 memulai dari posisi keempat, selanjutnya hanya mengambil 5 karakter dari jumlah karakter - 5, misal 10 - 5 =5 LEFT mengambil 5 karakter awal dari kiri dari posisi 4 substring, atau jika 8 - 5 = 3 maka LEFT mengambil hanya 3 karakter dari posisi awal substring

Proses diatas cukup kompleks. Sehingga Anda perlu menggunakan fungsi SUBSTRING() yang memiliki argumen opsional yang menerapkan panjang hasil yang diinginkan, dan Anda mengetahui bahwa panjang dari bagian tengah sama dengan panjang string dikurangi lima (tiga untuk karakater - karakter di awal dan dua untuk karakter - karakter diakhir).

Query berikut akan menjelaskan bagaimana mendapatkan bagian tengah numerik

MariaDB [root93]> SELECT id, SUBSTRING(id,4, LENGTH(id)-5) FROM rumah2;
+------------+-------------------------------+
| id         | SUBSTRING(id,4, LENGTH(id)-5) |
+------------+-------------------------------+
| RMM40672ID | 40672                         |
| DPR372UK   | 372                           |
| DPR1729JP  | 1729                          |
| TDR38SG    | 38                            |
| MND485US   | 485                           |
| MND415ID   | 415                           |
+------------+-------------------------------+
6 rows in set (0.00 sec)

Jadi bila dijelaskan secara lebih detail bagaimana proses diatas bisa menghasilkan panjang nilai yang diinginkan dimana dua karakter diakhir dibuang dan hanya menghasilkan nilai numerik

Panjang numerik yang akan diambil adalah sebanyak 5 karakter
Dalam contoh Id RMM40672ID memiliki panjang 10 karakter
Maka 10 - 5 = 5 sehingga SUBSTRING secara tidak langsung membentuk fungsi SUBSTRING(id,4,5) yang artinya fungsi melompati atau memulai nilainya dari string/posisi ke empat (4), dan ambil hanya sebanyak 5 karakter setelahnya (4,5)
Selanjutnya bagaimana jika pada kode id DPR372UK yang memiliki 8 karakter ? jawabannya adalah 8 -5 = 3, maka substring akan menghasilkan fungsi SUBSTRING(id,4,3), memulai posisinya dari karakter keempat,dan tampilkan hanya 3 karakter dari posisi awalnya
Jadi disini rumusnya adalah, jumlah string - jumlah string yang akan diambil

Selanjutnya meskipun kita sudah berhasil memecah ataupun mengekstrak bagian numerik dari ID, tetapi sayangnya nilainya tidak diurutkan secara numerik melainkan secara leksikal karena nilai - nilai yang dihasilkannya berupa string, oleh karena Anda perlu mengkonversinya supaya diurutkan secara numerik dengan cara menambahkan +0 didalam klausa ORDER by, hal ini sudah pernah dijelaskan pada pembelajaran sebelumnya [ Baca : Menampilkan Himpunan Nilai dengan Pengurutan Lain - MySQL Dasar #36 ]

MariaDB [root93]> SELECT id, SUBSTRING(id,4,LENGTH(id)-5) AS hasil_ekstraksi FROM rumah2 ORDER by hasil_ekstraksi;
+------------+-----------------+
| id         | hasil_ekstraksi |
+------------+-----------------+
| DPR1729JP  | 1729            |
| DPR372UK   | 372             |
| TDR38SG    | 38              |
| RMM40672ID | 40672           |
| MND415ID   | 415             |
| MND485US   | 485             |
+------------+-----------------+
6 rows in set (0.00 sec)

MariaDB [root93]> SELECT id, SUBSTRING(id,4,LENGTH(id)-5) AS hasil_ekstraksi FROM rumah2 ORDER by hasil_ekstraksi+0;
+------------+-----------------+
| id         | hasil_ekstraksi |
+------------+-----------------+
| TDR38SG    | 38              |
| DPR372UK   | 372             |
| MND415ID   | 415             |
| MND485US   | 485             |
| DPR1729JP  | 1729            |
| RMM40672ID | 40672           |
+------------+-----------------+
6 rows in set (0.00 sec)

MariaDB [root93]> SELECT id, deskripsi FROM rumah2 ORDER by SUBSTRING(id,4, LENGTH(id)-5)+0;
+------------+----------------+
| id         | deskripsi      |
+------------+----------------+
| TDR38SG    | lampu tidur    |
| DPR372UK   | oven microwave |
| MND415ID   | bath tube      |
| MND485US   | shower         |
| DPR1729JP  | kulkas         |
| RMM40672ID | meja makan     |
+------------+----------------+
6 rows in set (0.00 sec)

Tetapi untuk kasus ini, ada solusi yang lebih sederhana. Anda tidak perlu menghitung panjang dari bagian numerik, karena operasi konversi string ke angka membuang sufiks non-numerik dan memberikan nilai - nilai yang diperlukan untuk pengurutan atas nomor serial panjang tak tetap dari nilai - nilai id. Sehingga parameter optional ketiga dari fungsi SUBSTRING() tidak perlu ditambahkan saat melakukan pengurutan seperti contoh berikut ini

MariaDB [root93]> SELECT * FROM rumah2 ORDER by SUBSTRING(id,4)+0;
+------------+----------------+
| id         | deskripsi      |
+------------+----------------+
| TDR38SG    | lampu tidur    |
| DPR372UK   | oven microwave |
| MND415ID   | bath tube      |
| MND485US   | shower         |
| DPR1729JP  | kulkas         |
| RMM40672ID | meja makan     |
+------------+----------------+
6 rows in set, 6 warnings (0.00 sec)

Pada contoh lain Anda bisa menggunakan karakter delimeter untuk memecah nilai - nilai kolom. Saya asumsikan saja disini misalnya Anda sudah memiliki tabel baru dengan nama rumah3 dengan data nilai - nilai sebagai berikut

MariaDB [root93]> SELECT * FROM rumah3;
+---------------+----------------+
| id            | deskripsi      |
+---------------+----------------+
| 13-478-92-2   | meja makan     |
| 873-48-649-63 | oven microwave |
| 8-4-2-1       | kulkas         |
| 97-681-37-66  | lampu tidur    |
| 27-48-534-2   | shower         |
| 5764-56-89-72 | bath tube      |
+---------------+----------------+
6 rows in set (0.00 sec)

Untuk memecah nilai kolom menggunakan karakter delimeter, Anda bisa menggunakan fungsi SUBSTRING_INDEX() seperti dalam contoh berikut

MariaDB [root93]> SELECT id, deskripsi,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1) AS segmen2,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',4),'-',-1) AS segmen4
    -> FROM rumah3;
+---------------+----------------+---------+---------+
| id            | deskripsi      | segmen2 | segmen4 |
+---------------+----------------+---------+---------+
| 13-478-92-2   | meja makan     | 478     | 2       |
| 873-48-649-63 | oven microwave | 48      | 63      |
| 8-4-2-1       | kulkas         | 4       | 1       |
| 97-681-37-66  | lampu tidur    | 681     | 66      |
| 27-48-534-2   | shower         | 48      | 2       |
| 5764-56-89-72 | bath tube      | 56      | 72      |
+---------------+----------------+---------+---------+
6 rows in set (0.00 sec)

Untuk mengurutkan substring, Anda bisa menggunakan ekspresi tertentu didalam klausa ORDER by, Dua contoh query berikut akan mengurutkan berdasarkan segmen id kedua, dimana contoh pertama diurutkan secara leksikal dan yang kedua diurutkan secara numerik

MariaDB [root93]> SELECT id, deskripsi FROM rumah3
    -> ORDER by SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1);
+---------------+----------------+
| id            | deskripsi      |
+---------------+----------------+
| 8-4-2-1       | kulkas         |
| 13-478-92-2   | meja makan     |
| 873-48-649-63 | oven microwave |
| 27-48-534-2   | shower         |
| 5764-56-89-72 | bath tube      |
| 97-681-37-66  | lampu tidur    |
+---------------+----------------+
6 rows in set (0.00 sec)

MariaDB [root93]> SELECT id, deskripsi FROM rumah3
    -> ORDER by SUBSTRING_INDEX(SUBSTRING_INDEX(id,'-',2),'-',-1)+0;
+---------------+----------------+
| id            | deskripsi      |
+---------------+----------------+
| 8-4-2-1       | kulkas         |
| 873-48-649-63 | oven microwave |
| 27-48-534-2   | shower         |
| 5764-56-89-72 | bath tube      |
| 13-478-92-2   | meja makan     |
| 97-681-37-66  | lampu tidur    |
+---------------+----------------+
6 rows in set (0.00 sec)

Baca selanjutnya : Mengurutkan Nama Host - MySQL Dasar #45

Ahmad Zaelani

Seorang laki - laki biasa yang senang duduk sendirian di pinggir pantai, pengagum embun pagi dan lembayung senja, suka blogging, coding dan jogging. Moto hidupnya adalah hidup santai dan simple

Mengurutkan Berdasarkan Panjang Substring Berubah - MySQL Dasar #44

0 Response to "Mengurutkan Berdasarkan Panjang Substring Berubah - MySQL Dasar #44"

Post a Comment