Change the file extension in the text column in MySQL?

To change the file extension in the text column, you can use UPDATE command along with REPLACE() function. Let’s say we have some columns with extensions and we need to replace all of them. For that, let us first create a table with the extension columns set as text type:

mysql create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ProgramExtension1 text,
   ProgramExtension2 text,
   ImageExtension text
);
Query OK, 0 rows affected (0.52 sec)

Following is the query to insert records in the table using insert command:

mysql> insert into
DemoTable(ProgramExtension1,ProgramExtension2,ImageExtension)values('.java','.c','.jpeg');
Query OK, 1 row affected (0.18 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output:

+----+-------------------+-------------------+----------------+
| Id | ProgramExtension1 | ProgramExtension2 | ImageExtension |
+----+-------------------+-------------------+----------------+
|  1 | .java             |                .c |          .jpeg |
+----+-------------------+-------------------+----------------+
1 row in set (0.00 sec)

Following is the query to change the file extension in the text column:

mysql> update DemoTable
   set ProgramExtension1=replace(ProgramExtension1,'.java','.py'),
   ProgramExtension2=replace(ProgramExtension2,'.c','.cpp'),
   ImageExtension=replace(ImageExtension,'.jpeg','.png');
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Let us check the file extensions have been changed or not:

mysql> select *from DemoTable;

This will produce the following output

+----+-------------------+-------------------+----------------+
| Id | ProgramExtension1 | ProgramExtension2 | ImageExtension |
+----+-------------------+-------------------+----------------+
|  1 |               .py |              .cpp |           .png |
+----+-------------------+-------------------+----------------+
1 row in set (0.00 sec)

Krantik Chavan

e

Updated on: 30-Jul-2019

314 Views

Kickstart Your Career

Get certified by completing the course

Get Started