MySQL - INSERT() Function

The MySQL INSERT() function is used to insert a new string into the given string at a specified position and for a certain number of characters.

If you provide the position outside the length of string, it returns the string without any change. If you provide the number higher than the length of rest of the string, it replaces the string from position until the end of string.

Syntax

Following is the syntax of MySQL INSERT() function −

INSERT(str,pos,len,newstr)

Parameters

Following are the parameters accepted by this function −

  • str is the string value in which you need to insert a new sub string.

  • pos is the position at which the substring needs to be added.

  • len is the length of the of new string up to which it is to be inserted.

  • newstr is the new string that is to be inserted.

Return Value

This function returns a new string with the specified substring replaced by another string.

Example

In the following example, we are inserting the substring 'Tutorials' into the string 'Tutorialspoint' at position 10, replacing 3 characters −

SELECT INSERT('Tutorialspoint', 10, 3, 'Tutorials');

Following is the output of the above code −

INSERT('Tutorialspoint', 10, 3, 'Tutorials')
TutorialsTutorialsnt

Example

If the 'pos' value is not greater than the length of the original string, the function returns the original string −

SELECT INSERT('Tutorialspoint', 20, 5, 'Tutorials');

The output obtained is as follows −

INSERT('Tutorialspoint', 20, 5, 'Tutorials')
Tutorialspoint

Example

If the 'len' value is not greater than the length of the original string, the function replaces the rest of the original string with the new string −

SELECT INSERT('Tutorialspoint', 5, 20, ' welcome ');

We get the output as follows −

INSERT('Tutorialspoint', 5, 20, ' welcome ')
Tuto welcome

Example

If any of the arguments passed to this function is NULL, the function returns NULL −

SELECT INSERT('NULL', 5, 20, ' welcome ');

The result produced is as shown below −

INSERT('NULL', 5, 20, ' welcome ')
NULL

Example

You can also replace the values of a column of a table with a desired string using the INSERT() function.

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME LAST_NAME AGE INCOME
Krishna Sharma 19 2000
Raj Kandukuri 20 7000
Ramya Ramapriya 25 5000
Mac Mohan 26 2000

Following query replaces the values in the 'FIRST_NAME' column with a common string −

SELECT FIRST_NAME, LAST_NAME, AGE, INSERT(FIRST_NAME, 2, 50, ' REST OF THE STRING') as RESULT
FROM EMP;

After executing the above code, we get the following output −

FIRST_NAME LAST_NAME AGE RESULT
Krishna Sharma 19 K REST OF THE STRING
Raj Kandukuri 20 R REST OF THE STRING
Ramya Ramapriya 25 R REST OF THE STRING
Mac Mohan 26 A REST OF THE STRING
mysql-insert-function.htm
Advertisements