INSERT() String Function in SQL

The INSERT() function in SQL is a string function which is used to replace a specified number of characters in a string with another string. It takes the starting position as well as the number of characters to replace as arguments and returns the new modified string.

The INSERT() string function is mainly utilized in MySQL to modify or replace a part of an existing string without changing the original value stored in the database.

What is the INSERT() Function in SQL?

The INSERT() in SQL is a string function by which one string is inserted into another string at a specified position. It replaces some specific number of characters starting from some position with a new set of characters.

Syntax of INSERT() Function

The syntax of the INSERT() string function is as follows:

INSERT(string, position, length, new_string)

Parameters

The function takes four parameters which are as follows:

string: It is the original string in which the alteration will be done.

position: It is the starting position from which the characters are replaced. The first character has position 1.

length: It specifies the number of characters to replace.

new_string: It is the string that replaces the specified characters.

Examples of INSERT() String Function in SQL

We will take the help of the sample table below named "customers" in the examples to illustrate the working of the INSERT() string function.

Table: customers

Example 1: Replacing Characters in a String

The INSERT() function can be utilized to replace a specific number of characters in a string. In this example, we replace the word 'World' with SQL.

SQL Query:

SELECT INSERT('Hello World', 7, 5, 'SQL') AS Result;

Output:

Hello SQL

Explanation:

In the above example, 7 is the starting position, 5 signifies the number of characters to replace and 'SQL' is the new string.

Example 2: Inserting Text without Replacing Characters

The INSERT() function can also be used to insert text into a string without removing any existing characters. To do this, we can specify 0 as the number of characters to replace.

SQL Query:

SELECT INSERT('Hello World', 6, 0, ' SQL') AS Result;

Output:

Hello SQL World

Explanation:

The function starts at position 6 and replaces 0 characters. Therefore 'SQL' is inserted at that position without removing any existing characters.

Example 3: Using INSERT() with a Table Column

We can also utilize the INSERT() function with table columns. Suppose we want to replace the first three digits of each customer's phone number with XXX.

SQL Query:

SELECT CustomerName,
INSERT(Phone, 1, 3, 'XXX') AS MaskedPhone
FROM Customers;

Output:

Explanation:

In the above example, 1 is the starting position and 3 specifies the number of characters that need to be replaced in the Phone column with XXX. It masks the first three digits of each phone number.

Example 4: Replacing Part of an Email Address

We can use the function with an Email column to replace part of an email address. Here we will replace the first five characters with asterisks.

SQL Query:

SELECT CustomerName,
INSERT(Email, 1, 5, '*****') AS MaskedEmail
FROM Customers;

Output:

Explanation:

The function starts at position 1 and replaces the first 5 characters of the Email column with *****. The remaining characters remain unchanged.

Example 5: Replacing Characters at a Specific Position

We can utilize this function to replace characters from any position in a string. In this example, we will replace the word 'Tutorial' with 'Function'.

SQL Query:

SELECT INSERT('SQL Tutorial', 5, 8, 'Function') AS Result;

Output:

SQL Function

Explanation:

Here the function starts at position 5 and replaces 8 characters. The eight characters in 'Tutorial' are replaced with 'Function'.

Example 6: INSERT() Function with NULL Values

The INSERT() function returns NULL when the original string is NULL.

SQL Query:

SELECT INSERT(NULL, 1, 3, 'SQL') AS Result;

Output:

NULL

Explanation:

Since the original string is NULL then the INSERT() function returns NULL. Similarly, if the new string is NULL then the function also returns NULL.

Difference Between INSERT() and CONCAT() in SQL

Both INSERT() and CONCAT() are string functions but they perform different operations which we will see in the difference table below:

Difference Between INSERT() and REPLACE() in SQL

The INSERT() and REPLACE() functions can both be used to modify strings but they work differently. Below is a difference table: