MySQL INSERT() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL INSERT() Function

The INSERT() function in MySQL is used to modify a string by inserting a substring (string2) at a specified position, replacing a certain number of characters.

Syntax

INSERT(string, position, number, string2)

Parameters

Parameter Description
string Required. The original string to be modified.
position Required. The position in string where string2 will be inserted.
number Required. The number of characters to replace in string.
string2 Required. The string to insert into string.

Definition and Use

  • Purpose: Replaces a specified number of characters in a string starting from a given position with another string.
  • Behavior:
    • If position exceeds the length of the original string, the function returns the original string.
    • If number is greater than the length of the remaining string from the specified position, the function replaces characters from position to the end of the string.

Usage Examples

Example 1: Basic Insertion

Replace the first nine characters of "W3Schools.com" with "Example":

SELECT INSERT("W3Schools.com", 1, 9, "Example") AS ModifiedString;

Output:

ModifiedString
Example.com

Explanation: "Example" replaces the first nine characters of "W3Schools.com".

Example 2: Replace Characters at a Specific Position

Replace three characters, starting from position 11, with "no":

SELECT INSERT("W3Schools.com", 11, 3, "no") AS ModifiedString;

Output:

ModifiedString
W3Schools.cno

Explanation: At position 11 (the character "o" in "com"), three characters are replaced with "no".

Example 3: Position Exceeds String Length

Attempt to insert at a position beyond the length of the original string:

SELECT INSERT("Hello", 10, 2, "World") AS ModifiedString;

Output:

ModifiedString
Hello

Explanation: Since position 10 exceeds the length of "Hello", the original string is returned.

Example 4: Replace Beyond the Remaining String Length

Replace characters beyond the length of the remaining string:

SELECT INSERT("Database", 5, 10, "Management") AS ModifiedString;

Output:

ModifiedString
DataManagement

Explanation: Starting from position 5, all characters to the end of "Database" are replaced with "Management".

Technical Details

  • Availability: Available from MySQL 4.0 onwards.
  • Return Type: Returns the modified string.
  • Character Counting: Positions and counts are 1-based (the first character is at position 1).

Applications

  1. String Modification: Dynamically replace parts of strings for user input or data manipulation.
  2. Text Substitution: Update specific sections of text in a database.
  3. Data Formatting: Insert or replace portions of strings for display or reporting purposes.

Key Notes

  • The function is case-sensitive, so replacement is exact.
  • If no replacement is required, set number to 0 to insert the new string without removing characters.
  • The original string is not permanently altered—only the result of the query is affected.

The INSERT() function is a versatile tool for handling and modifying strings in MySQL, offering precise control over substring replacement and insertion.

Was this article helpful?