Prerequisities
- Berdoa sebelum melakukan aktifitas
- Install database MySQL
- Kopi dan Snack untuk menemani aktifitas
Persiapan
- Buat tabel Pegawai dengan menjalankan script di bawah ini
CREATE TABLE `pegawai` ( `id_pegawai` varchar(10) NOT NULL, `nama_pegawai` varchar(30) NOT NULL, `gender` char(1) NOT NULL, `umur` int(3) NOT NULL, `alamat` varchar(30) NOT NULL, `kd_departemen` varchar(5) DEFAULT NULL, PRIMARY KEY (`id_pegawai`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- Insert data tabel Pegawai dengan menjalankan script di bawah ini
insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('001','Nursalim','L','20','Brebes','IT'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('002','Iskiyati','P','25','Bandung','IT'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('003','Nurul Hikmah','P','18','Semarang','ACC'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('004','Naura Krasiva','P','15','Jakarta','IT'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('005','Ahmad Fathoni','L','20','Yogyakarta','HR'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('006','Ramanata','L','17','Jakarta','HR'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('007','Taryono','L','18','Surabaya','ACC'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('008','Nani Indriyani','P','15','Bandung','FIN'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('009','Imron','L','23','Bandung','FIN'); insert into `pegawai` (`id_pegawai`, `nama_pegawai`, `gender`, `umur`, `alamat`, `kd_departemen`) values('010','Andi','L','16','Bandung','IT');
- Tampilkan data dari tabel Pegawai
mysql> SELECT * FROM pegawai; +------------+----------------+--------+------+------------+---------------+ | id_pegawai | nama_pegawai | gender | umur | alamat | kd_departemen | +------------+----------------+--------+------+------------+---------------+ | 001 | Nursalim | L | 20 | Brebes | IT | | 002 | Iskiyati | P | 25 | Bandung | IT | | 003 | Nurul Hikmah | P | 18 | Semarang | ACC | | 004 | Naura Krasiva | P | 15 | Jakarta | IT | | 005 | Ahmad Fathoni | L | 20 | Yogyakarta | HR | | 006 | Ramanata | L | 17 | Jakarta | HR | | 007 | Taryono | L | 18 | Surabaya | ACC | | 008 | Nani Indriyani | P | 15 | Bandung | FIN | | 009 | Imron | L | 23 | Bandung | FIN | | 010 | Andi | L | 16 | Bandung | IT | +------------+----------------+--------+------+------------+---------------+ 10 rows in set (0.00 sec)
CONCAT
Fungsi CONCAT() digunakan untuk menggabungkan 2 string (kata).
Sintak Dasar
SQL> SELECT CONCAT(string1, string2)
FROM table_name;
Contoh:
mysql> SELECT CONCAT(nama_pegawai, alamat)
-> FROM pegawai;
+------------------------------+
| CONCAT(nama_pegawai, alamat) |
+------------------------------+
| NursalimBrebes |
| IskiyatiBandung |
| Nurul HikmahSemarang |
| Naura KrasivaJakarta |
| Ahmad FathoniYogyakarta |
| RamanataJakarta |
| TaryonoSurabaya |
| Nani IndriyaniBandung |
| ImronBandung |
| AndiBandung |
+------------------------------+
10 rows in set (0.00 sec)
mysql> SELECT CONCAT(nama_pegawai, ' - ', alamat)
-> FROM pegawai;
+-------------------------------------+
| CONCAT(nama_pegawai, ' - ', alamat) |
+-------------------------------------+
| Nursalim - Brebes |
| Iskiyati - Bandung |
| Nurul Hikmah - Semarang |
| Naura Krasiva - Jakarta |
| Ahmad Fathoni - Yogyakarta |
| Ramanata - Jakarta |
| Taryono - Surabaya |
| Nani Indriyani - Bandung |
| Imron - Bandung |
| Andi - Bandung |
+-------------------------------------+
10 rows in set (0.00 sec)
CONCAT_WS
Fungsi CONCAT_WS() digunakan untuk menggabungkan 2 string (kata) dengan kata pemisah (separator)
SQL> SELECT CONCAT_WS(separator, string1, string2, ...)
FROM table_name;
Contoh :
mysql> SELECT CONCAT_WS(' dari ', nama_pegawai, alamat)
-> FROM pegawai;
+-------------------------------------------+
| CONCAT_WS(' dari ', nama_pegawai, alamat) |
+-------------------------------------------+
| Nursalim dari Brebes |
| Iskiyati dari Bandung |
| Nurul Hikmah dari Semarang |
| Naura Krasiva dari Jakarta |
| Ahmad Fathoni dari Yogyakarta |
| Ramanata dari Jakarta |
| Taryono dari Surabaya |
| Nani Indriyani dari Bandung |
| Imron dari Bandung |
| Andi dari Bandung |
+-------------------------------------------+
10 rows in set (0.00 sec)
LENGTH
Fungsi LENGTH() digunakan untuk menampilkan panjang suatu kata (string).
Sintak Dasar
SQL> SELECT LENGTH(string)
FROM table_name;
Contoh
mysql> SELECT nama_pegawai, length(nama_pegawai), alamat, length(alamat)
-> FROM pegawai;
+----------------+----------------------+------------+----------------+
| nama_pegawai | length(nama_pegawai) | alamat | length(alamat) |
+----------------+----------------------+------------+----------------+
| Nursalim | 8 | Brebes | 6 |
| Iskiyati | 8 | Bandung | 7 |
| Nurul Hikmah | 12 | Semarang | 8 |
| Naura Krasiva | 13 | Jakarta | 7 |
| Ahmad Fathoni | 13 | Yogyakarta | 10 |
| Ramanata | 8 | Jakarta | 7 |
| Taryono | 7 | Surabaya | 8 |
| Nani Indriyani | 14 | Bandung | 7 |
| Imron | 5 | Bandung | 7 |
| Andi | 4 | Bandung | 7 |
+----------------+----------------------+------------+----------------+
10 rows in set (0.00 sec)
REPLACE
Fungsi REPLACE() digunakan untuk mengganti suatu string atau kata dengan string lainnya.
Sintak Dasar
REPLACE(field_name,string_to_find,string_to_replace)
Contoh
mysql> SELECT nama_pegawai, replace(nama_pegawai,'a','*')
-> FROM pegawai;
+----------------+-------------------------------+
| nama_pegawai | replace(nama_pegawai,'a','*') |
+----------------+-------------------------------+
| Nursalim | Nurs*lim |
| Iskiyati | Iskiy*ti |
| Nurul Hikmah | Nurul Hikm*h |
| Naura Krasiva | N*ur* Kr*siv* |
| Ahmad Fathoni | Ahm*d F*thoni |
| Ramanata | R*m*n*t* |
| Taryono | T*ryono |
| Nani Indriyani | N*ni Indriy*ni |
| Imron | Imron |
| Andi | Andi |
+----------------+-------------------------------+
10 rows in set (0.00 sec)
SUBSTRING atau SUBSTR
Fungsi SUBSTRING() atau SUBSTR() digunakan untuk mengekstrak atau mengambil bagian suatu string dari sebuah string atau kata dengan menggunakan posisi tertentu.
Sintak Dasar
SUBSTR(string, position); SUBSTR(string FROM position); SUBSTR(string, position, length);
Contoh:
mysql> SELECT nama_pegawai, SUBSTRING(nama_pegawai,3)
-> FROM pegawai;
+----------------+---------------------------+
| nama_pegawai | SUBSTRING(nama_pegawai,3) |
+----------------+---------------------------+
| Nursalim | rsalim |
| Iskiyati | kiyati |
| Nurul Hikmah | rul Hikmah |
| Naura Krasiva | ura Krasiva |
| Ahmad Fathoni | mad Fathoni |
| Ramanata | manata |
| Taryono | ryono |
| Nani Indriyani | ni Indriyani |
| Imron | ron |
| Andi | di |
+----------------+---------------------------+
10 rows in set (0.00 sec)
mysql> SELECT nama_pegawai, SUBSTRING(nama_pegawai FROM 2)
-> FROM pegawai;
+----------------+--------------------------------+
| nama_pegawai | SUBSTRING(nama_pegawai FROM 2) |
+----------------+--------------------------------+
| Nursalim | ursalim |
| Iskiyati | skiyati |
| Nurul Hikmah | urul Hikmah |
| Naura Krasiva | aura Krasiva |
| Ahmad Fathoni | hmad Fathoni |
| Ramanata | amanata |
| Taryono | aryono |
| Nani Indriyani | ani Indriyani |
| Imron | mron |
| Andi | ndi |
+----------------+--------------------------------+
10 rows in set (0.00 sec)
mysql> SELECT nama_pegawai, SUBSTRING(nama_pegawai, 3, 7)
-> FROM pegawai;
+----------------+-------------------------------+
| nama_pegawai | SUBSTRING(nama_pegawai, 3, 7) |
+----------------+-------------------------------+
| Nursalim | rsalim |
| Iskiyati | kiyati |
| Nurul Hikmah | rul Hik |
| Naura Krasiva | ura Kra |
| Ahmad Fathoni | mad Fat |
| Ramanata | manata |
| Taryono | ryono |
| Nani Indriyani | ni Indr |
| Imron | ron |
| Andi | di |
+----------------+-------------------------------+
10 rows in set (0.00 sec)
mysql> SELECT nama_pegawai, SUBSTRING(nama_pegawai, -8)
-> FROM pegawai;
+----------------+-----------------------------+
| nama_pegawai | SUBSTRING(nama_pegawai, -8) |
+----------------+-----------------------------+
| Nursalim | Nursalim |
| Iskiyati | Iskiyati |
| Nurul Hikmah | l Hikmah |
| Naura Krasiva | Krasiva |
| Ahmad Fathoni | Fathoni |
| Ramanata | Ramanata |
| Taryono | |
| Nani Indriyani | ndriyani |
| Imron | |
| Andi | |
+----------------+-----------------------------+
10 rows in set (0.00 sec)
~~~## MySQL Tutorial##~~~
Sekian tutorial singkat tentang Mengenal Fungsi String Populer Di MySQL. Semoga bermanfaat & Happy Learning MySQL.
Written by Unknown
We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.
0 comments:
Post a Comment