首页 >数据库 >mysql教程 >MySQL – How INSERT() Function Works for MySQL_MySQL

MySQL – How INSERT() Function Works for MySQL_MySQL

WBOY
WBOY原创
2016-05-31 08:46:12941浏览

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” .

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 )

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn