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

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• How to change file extension in Python?
• Get only the file extension from a column with file names as strings in MySQL?
• Perl File Extension
• Change a MySQL Column datatype from text to timestamp?
• How to get the file extension using PowerShell?
• Get file extension name in Java
• How MySQL evaluates the blank line between two lines written in the text file while importing that text file into MySQL table?
• What is the use of escape character (\) in text file while importing the data from text file to MySQL table?
• Change the column name from StudentName to FirstName in MySQL?
• C# program to get the extension of a file in C#
• How can we upload the changed value, rather than written in a text file, of column(s) while importing that text file into MySQL table?
• How to get file extension of file as a result of MySQL query?
• How can we change the data type of the column in MySQL table?
• Concatenate the column values with separate text in MySQL and display in a single column
• Insert a word before the file name’s dot extension in JavaScript?