How to remove partial text from value in MySQL?

In order to remove partial text from value, you can use REPLACE() from MySQL. Following is the syntax −

update yourTableName set yourColumnName = replace(yourColumnName ,'yourValue ', '' );

Let us first create a table −

mysql> create table removePartialTextDemo
   -> (
   -> JavaVersionDetails varchar(100)
   -> );
Query OK, 0 rows affected (0.51 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into removePartialTextDemo values('Java Version 1.0');
Query OK, 1 row affected (0.50 sec)

mysql> insert into removePartialTextDemo values('Java Version 1.1');
Query OK, 1 row affected (0.23 sec)

mysql> insert into removePartialTextDemo values('Java Version 1.2');
Query OK, 1 row affected (0.16 sec)

mysql> insert into removePartialTextDemo values('Java Version 1.3');
Query OK, 1 row affected (0.27 sec)

mysql> insert into removePartialTextDemo values('Java Version 1.4');
Query OK, 1 row affected (0.15 sec)

mysql> insert into removePartialTextDemo values('Java Version 7');
Query OK, 1 row affected (0.11 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from removePartialTextDemo;

This will produce the following output −

+--------------------+
| JavaVersionDetails |
+--------------------+
| Java Version 1.0   |
| Java Version 1.1   |
| Java Version 1.2   |
| Java Version 1.3   |
| Java Version 1.4   |
| Java Version 7     |
+--------------------+
6 rows in set (0.00 sec)

Here is the query to remove partial text from value. We are separating the version number here −

mysql> update removePartialTextDemo set
JavaVersionDetails = replace(JavaVersionDetails,'Java Version ','');
Query OK, 6 rows affected (0.09 sec)
Rows matched: 6 Changed: 6 Warnings: 0

Let us display all records from the table to check the partial text have been removed −

mysql> select * from removePartialTextDemo;

This will produce the following output −

+--------------------+
| JavaVersionDetails |
+--------------------+
| 1.0                |
| 1.1                |
| 1.2                |
| 1.3                |
| 1.4                |
| 7                  |
+--------------------+
6 rows in set (0.00 sec)

Smita Kapse

Published on 15-Apr-2019 14:02:25

Previous Page Print Page