MSSQL STUFF function and it's equivalent in MySQL

The STUFF function embeds a string within another string by replacing a specified number of characters. The same functionality is provided by INSERT function in MySQL. Here are the details and examples

MSSQL

Syntax

STUFF ( character_expression , start , length , replaceWith_expression )

Example

In the following query we are embedding '1234' at second position by replacing characters 'bcd' in the string 'abcdefgh'

select stuff('abcdefgh',2,3,'1234')
-----------------------
a1234efgh

MySQL

In MySQL the same functionality is provided by INSERT function.

Syntax

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

Example

mysql> select INSERT('12345678',2,3,'abc');
+------------------------------+
| INSERT('12345678',2,3,'abc') |
+------------------------------+
| 1abc5678 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select INSERT('abcdefgh',2,3,'1234');
+-------------------------------+
| INSERT('abcdefgh',2,3,'1234') |
+-------------------------------+
| a1234efgh |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

Back to Converting Functions from MSSQL to MySQL