SQL Tutorial SQL Advanced SQL Database SQL References

MySQL INSERT() Function



The MySQL INSERT() function inserts a substring into a string at a specified position for a certain number of characters.

If the specified position is not within the length of the string, this function will return the string. If the number is not within the length of the rest of the string, this function will replace the string starting from position until the end of the string. Note that the first position in string starts with 1.

Syntax

INSERT(string, position, number, substring)

Parameters

string Required. Specify the string to modify.
position Required. Specify the position in string to insert substring.
number Required. Specify the number of characters to replace in string.
substring Required. Specify the substring to insert into string.

Return Value

Returns the modified string.

Example 1:

The example below shows the usage of INSERT() function.

mysql> SELECT INSERT('Learning SQL is fun', 10, 3, 'MySQL');
Result: 'Learning MySQL is fun'

mysql> SELECT INSERT('abcxyz', 4, 3, 'def');
Result: 'abcdef'

mysql> SELECT INSERT('ABCPQR', 4, 3, 'abc');
Result: 'ABCabc'

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCitySalary
FIN001JohnLondon3000
FIN002MarryNew York2750
FIN003JoParis2800
FIN004KimAmsterdam3100
FIN005RameshNew Delhi3000
FIN006HuangBeijing2800

In the below query, the INSERT() function is used to change the records of EmpID column of the Employee table:

UPDATE Employee SET EmpID = INSERT(EmpID, 1, 3, 'FINCAD');

-- see the result
SELECT * FROM Employee;

This will produce the following result:

EmpIDNameCitySalary
FINCAD001JohnLondon3000
FINCAD002MarryNew York2750
FINCAD003JoParis2800
FINCAD004KimAmsterdam3100
FINCAD005RameshNew Delhi3000
FINCAD006HuangBeijing2800

❮ MySQL Functions