MySQL INSERT() Function

 The INSERT() function in MySQL is used to insert a string into another string at a specified position. It can also replace a portion of the original string with the new string.

Syntax:

INSERT(original_string, position, length, new_string)

• original_string: The string to modify.
• position: The position where the insertion begins (1-based index).
• length: The number of characters in the original string to be replaced.
• new_string: The string to insert into the original string.

Key Points:

1. If position is greater than the length of original_string, the new_string is appended.
2. If length is 0, the function simply inserts the new_string at the specified position without removing characters.
3. If length exceeds the length of the remaining characters in original_string from the position, it removes all the remaining characters.

Examples:

Example 1: Basic insertion

SELECT INSERT('Hello World', 7, 0, 'Beautiful ');
-- Output: 'Hello Beautiful World'

Example 2: Replacing a portion of the string

SELECT INSERT('Hello World', 7, 5, 'Universe');
-- Output: 'Hello Universe'

Example 3: Appending a string

SELECT INSERT('Hello', 10, 0, ' World');
-- Output: 'Hello World'

Example 4: Replacing more characters than available

SELECT INSERT('Hello', 4, 10, 'p!');
-- Output: 'Help!'

This function is particularly useful for manipulating strings in queries, such as formatting data or dynamically generating output.