In this tutorial, we will learn about the MySQL INSERT() function. Suppose you have a string ‘Java is my favorite programming language’. However, Java isn’t my only favorite programming language.
I love MySQL too and so I want to change the string to ‘Java and MySQL are my favourite programming language’. This is where the MySQL INSERT() function comes into the picture. The INSERT() function is used to insert a string within a string at a specific position and for a given number of characters.
Syntax of MySQL INSERT()
INSERT(original_string, position, number_of_characters, string2)Code language: SQL (Structured Query Language) (sql)Where,
- ‘Original_string’ is the string in which you want to insert another string,
- ‘Position’ is the position in ‘original_string’ at which ‘string2’ should be inserted,
- ‘Number_of_characters’ is the number of characters that you want to be replaced by the inserted string, and,
- ‘String2’ is the string that is to be inserted.
Basic Examples of MySQL INSERT()
Let us start with a basic example. I have the following string – ‘Her name is Radha.’ and I want to insert the name ‘Medha’ by replacing ‘Radha’. The query for it is,
SELECT INSERT(“Her name is Radha.”, 13, 5, “Medha”) AS StringAfterInsert;Code language: SQL (Structured Query Language) (sql)So, we specify the position as 13 which is the character ‘R’. We also specify that we want to replace 5 characters (including the character at position 13) with the word ‘Medha’. The result is displayed using the SELECT statement, under the alias of StringAfterResult. So we get our output as,
Inserting at the beginning of the String with MySQL INSERT()
Let us take another example. We have the following string – ‘South America is a continent.’ I want to replace the South and insert ‘North’ in there instead. We do this using the query,
SELECT INSERT(“South America is a continent.”, 1, 5, “North”) AS StringAfterInsert;Code language: SQL (Structured Query Language) (sql)And we get our output as,
The characters to be replaced can be any number of characters. For example, in the string from the previous example, you can replace the entire string ‘South America’ and insert ‘Asia’ over there instead.
SELECT INSERT(“South America is a continent.”, 1, 13, “Asia”) AS StringAfterInsert;Code language: SQL (Structured Query Language) (sql)So now, we have said we want to start at position 1 and replace 13 characters from there with Asia. Our output is,
Negative Position with MySQL INSERT()
What if you specify the position parameter as a negative integer? Let’s take a look at such an example.
SELECT INSERT(“This string”, -5, 13, “Word”) AS StringAfterInsert;Code language: SQL (Structured Query Language) (sql)And the output is,
Whoops! This is because there is no such thing as a negative position in MySQL. Since the position parameter is not recognized, the insertion does not take place.
Number of Characters To Be Replaced Exceeds Length Of the String
What if your string is of the length 11 but you mention 5 as your position parameter and 15 as the number of characters to be replaced? Wait.
The string length is 11, how will it replace 15 characters when there are only 11 and that too, replacement starts on position 5?
In such a case, MySQL just continues to remove the characters till the end of the string and inserts the given character over there, and gives you the desired output. Let us demonstrate this using the below example –
SELECT INSERT(“This string”, 5, 15, “Word”) AS StringAfterInsert;Code language: SQL (Structured Query Language) (sql)And the output is,
Examples of MySQL INSERT() – Working with Tables
Let’s build on what we learnt on the examples earlier and try other example types, this time on table data. Consider the below Employee table.
Inserting without replacing any character
Let us display a result under the alias NameOfEmployee which contains ‘Mr/Mrs ‘ inserted before the name of the employee. Since no character from the name should be replaced, the number of characters parameter of the MySQL INSERT() function should be 0. The query for it is:
SELECT INSERT(Name, 1, 0, “Mr/Mrs “) AS NameOfEmployee FROM Employee;Code language: SQL (Structured Query Language) (sql)And the output is,
MySQL INSERT() with WHERE Clause
Ritwik Pawar is the CEO of the company. He also holds a PhD from MIT. Let us write a query that displays only Ritwik’s name along with his title (Dr, since he has a PhD) under the alias CEO. We will filter through the table data using the WHERE clause.
SELECT INSERT(Name, 1, 0, “Dr “) AS CEO FROM Employee WHERE Name=’Ritwik Pawar’;Code language: SQL (Structured Query Language) (sql)And the output is,
Bonus: A Very Complex Example!
Okay, bear with me because this is one complex example.
Every employee has a unique code – the first letter of their Office_Code, eid, the month in which they joined followed by a ‘-’ and the first two characters of their department in uppercase.
For instance, if the employee has id – 7, and joined on 9th February 2020 and works in the Executive department in the Pune office (Office Code – PUN) then his unique code will be – P72-EX.
Let us write a query for the above for our Employee table. We will be making use of a lot of string functions like – UPPER(), CONCAT(), LEFT(). The MONTH() function returns the month number from a date value.
So, our query is –
SELECT Name, INSERT(Office_Code, 2, 2, CONCAT(eid, MONTH(Date_Joined), “-”, UPPER(LEFT(Department, 2)))) AS UniqueCode FROM Employee;Code language: SQL (Structured Query Language) (sql)And the output is,
Examples like these are what you will encounter in the real world while working with these string functions.
Conclusion
INSERT() is a powerful string function that can be used for a variety of use cases. I would recommend you play around with the function long enough to get a hang of it.