MySQL INSERT() Function

In MySQL, the INSERT() function is used to insert a string within a string and remove multiple characters from the original string.

INSERT(str,pos,len,newstr) is a Sring function of MySQL. In this method, a string str is passed with a position 'pos' which tells at which position the character is to be placed, and 'len' length is the length of the character to be placed i.e string newstr.

Syntax

In the above syntax,

• The select statement is used to retrieve data from a table.
• str represents the original string.
• pos represents the position of the string where the string is inserted.
• len represents the number of characters to be inserted.
• newstr represents the new string to be inserted.

Example 1

Explanation: On execution of the above statement, the INSERT function inserts the character 't' at position 5 in 'tpoinetech' string.

Output: The output of this example is given below.

Example 2

Explanation: On execution of the above statement, the new string 'String' has been inserted. The insertion starts from the 6th character of the original string, and this function removes the '- 'from the string.

Output: The output of this example is given below.

MySQL INSERT function with Update Statement

In MySQL, the INSERT() function does not change the data in the table itself. It returns an updated string, which can be used to update the table data in an UPDATE statement.

Consider the Teacher table, which contains the following information to be inserted using the MySQL INSERT function.

Syntax:

Example: Consider a MySQL Query using the INSERT() function to change the records of the TID column in the Teacher table.

Explanation: On running the above update query, the insert function inserts the string 'T10' into the TID column of the Teachers table and does not remove any characters from the string.

If you want to check whether the content has been updated in the table or not, use the following statement.

Output: After running this query, the output of the table is given below.

Frequently asked questions on MySQL INSERT Function

1. Write a MySQL statement to select the salary column from the Teacher table and insert the string ',' at the 3rd character position of each salary value.

Sample Table: Teacher Table

Solution:

Explanation: On executing the above statement, the INSERT function inserts the string ',' at the 3rd character position of each value of the Salary column. In this, 0 indicates non-removal of characters in the column value.

Output: The output of this example is given below.