Mysql : substring_index

By human

Cara menghilangkan karakter dikiri dan kanan hasil dari query.

Di mysql versi 5.1

hasil query sebelum di edit

mysql> select chanel from data2
    -> where chanel like 'SIP%' limit 10;
+-----------------------+
| chanel                |
+-----------------------+
| SIP/xx263031-b7d0a4e8 |
| SIP/xx263031-b7d125e0 |
| SIP/1234-b7d07058     |
| SIP/xx263031-b7d08a90 |
| SIP/1234-b7d08a90     |
| SIP/1234-b7d125e0     |
| SIP/xx263089-b7d125e0 |
| SIP/xx263003-b7d0d998 |
| SIP/xx263005-b7d08a90 |
| SIP/xx263000-b7d14038 |
+-----------------------+
10 rows in set (0.00 sec)

Untuk menghilangkan karakter SIP dan karakter random lainnya setelah nomer telepon menggunakan fungsi substring_index di mysql.
Karena ada di kiri dan kanan dari hasil tujuan, maka perlu dieksekusi 2 kali substring_index.

mysql> select substring_index((substring_index(chanel,'/',-1)),'-',1) as hasil
    -> from data2 where chanel like 'SIP%' limit 10;
+----------+
| hasil    |
+----------+
| xx263031 |
| xx263031 |
| 1234     |
| xx263031 |
| 1234     |
| 1234     |
| xx263089 |
| xx263003 |
| xx263005 |
| xx263000 |
+----------+
10 rows in set (0.00 sec)

No Comments

(Required)
(Required, will not be published)