How the INSERT() function works in Mariadb?

The MariaDB INSERT() function is used to insert a substring into a string at a specified position and for a certain length. This function is commonly utilized when there is a need to modify or format strings within SQL queries, such as updating parts of a string based on dynamic data inputs.

Syntax

MariaDB INSERT() function’s syntax is as follows:

INSERT(original_string, position, length, new_substring)

• original_string is the string that will be modified.
• position is the start position in the original string where the new substring will be inserted.
• length is the number of characters in the original string that will be removed.
• new_substring is the string that will be inserted into the original string.

The function returns the modified string with the new substring inserted.

Examples

Example 1: Basic Insertion

This example demonstrates inserting a substring into a string.

SELECT INSERT('Hello World!', 7, 5, 'MariaDB');

The output for this statement is:

+-----------------------------------------+
| INSERT('Hello World!', 7, 5, 'MariaDB') |
+-----------------------------------------+
| Hello MariaDB!                          |
+-----------------------------------------+

This result shows the word ‘World’ replaced by ‘MariaDB’ starting at the 7th position.

Example 2: Insertion with Zero Length

Illustrating insertion without removing any part of the original string.

SELECT INSERT('Hello World!', 7, 0, 'Beautiful ');

The output for this statement is:

+--------------------------------------------+
| INSERT('Hello World!', 7, 0, 'Beautiful ') |
+--------------------------------------------+
| Hello Beautiful World!                     |
+--------------------------------------------+

The phrase ‘Beautiful ’ is inserted without replacing any part of the original string.

Example 3: Insertion at the Beginning

Inserting a substring at the beginning of the original string.

SELECT INSERT('Hello World!', 1, 0, 'Welcome to ');

The output for this statement is:

+---------------------------------------------+
| INSERT('Hello World!', 1, 0, 'Welcome to ') |
+---------------------------------------------+
| Welcome to Hello World!                     |
+---------------------------------------------+

The phrase ‘Welcome to ’ is inserted at the beginning of the original string.

Example 4: Insertion at the End

Appending a substring at the end of the original string.

SELECT INSERT('Hello', 6, 0, ' World!');

The output for this statement is:

+----------------------------------+
| INSERT('Hello', 6, 0, ' World!') |
+----------------------------------+
| Hello                            |
+----------------------------------+

The phrase ’ World!’ is appended to the end of the original string.

Example 5: Insertion with Table Data

Using INSERT() to modify strings stored in a table.

DROP TABLE IF EXISTS greetings;
CREATE TABLE greetings (phrase VARCHAR(255));
INSERT INTO greetings (phrase) VALUES ('Hello World!');

SELECT phrase, INSERT(phrase, 7, 5, 'MariaDB') AS modified_phrase FROM greetings;

The output for this statement is:

+--------------+-----------------+
| phrase       | modified_phrase |
+--------------+-----------------+
| Hello World! | Hello MariaDB!  |
+--------------+-----------------+

This table shows the original phrases and their modified versions after using the INSERT() function.

Related Functions

Below are a few functions related to the MariaDB INSERT() function:

• MariaDB REPLACE() function is used to replace occurrences of a specified substring within a string with another substring.
• MariaDB SUBSTRING() function is used to extract a part of a string.
• MariaDB CONCAT() function is used to concatenate two or more strings into one string.

Conclusion

The INSERT() function in MariaDB is a versatile tool for string manipulation, allowing for precise modifications to strings by inserting substrings at specified positions. Its ability to alter strings dynamically makes it a valuable function for various SQL operations and data formatting tasks.