penasaran.com - query bodoh2an untuk mengganti string tertentu di mysql

hasil.sql

mysql> create table anu_table (
    ->   id int auto_increment primary key,
    ->   ukuran varchar(50) null
    -> ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into anu_table (ukuran)
    -> values ('200mm2'), ('250mm2'), ('20 x 50 mm2'), ('1 x 2 mm'), ('1 x 5mm2'), ('1 m2') ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from anu_table;
+----+-------------+
| id | ukuran      |
+----+-------------+
|  1 | 200mm2      |
|  2 | 250mm2      |
|  3 | 20 x 50 mm2 |
|  4 | 1 x 2 mm    |
|  5 | 1 x 5mm2    |
|  6 | 1 m2        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> select id, concat(trim(substr(ukuran,1,length(ukuran)-3)),' mm^2') hasil
    -> from anu_table
    -> where ukuran like '%mm2' ;
+----+--------------+
| id | hasil        |
+----+--------------+
|  1 | 200 mm^2     |
|  2 | 250 mm^2     |
|  3 | 20 x 50 mm^2 |
|  5 | 1 x 5 mm^2   |
+----+--------------+
4 rows in set (0.00 sec)

mysql>