欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL – How INSERT() Function Works for MySQL_MySQL

程序员文章站 2022-06-10 12:38:33
...
You know there is an INSERT statement which is used to add data to a table. But did you know that there is an INSERT function which will replace certain characters within a string?

INSERT ( ) function in MySQL is used to insert a substring at the specified position up to the specified number of characters.

Let us consider this example.

set @str:='This is nothing';

The variable @str has a string value “This is nothing”. Suppose you want to change “nothing” into “everything” in this string. You can use INSERT ( ) function as shown below

select INSERT(@str,9,2,'every');

The INSERT ( ) function replaces the characters starting from the position 9 to next 2 characters by “everything”. So in the string part “nothing”, no is replaced by “every” and the full string becomes “This is everything” .

MySQL – How INSERT() Function Works for MySQL_MySQL

You can also add a string between two strings as shown below

set @str:='This is nothing'; select INSERT(@str,9,0,'not ');

The result is “This is not nothing”. When the third parameter is 0, it will not replace any characters, but append it at the starting position defined in the second parameter

Note: This INSERT ( ) function is functionally equivalent to SQL Server’s STUFF ( ) function. You can refer the usage of STUFF ( ) function in this post .

Reference: Pinal Dave ( http://blog.sqlauthority.com )