摘要:本文主要向大家介绍了MySQL数据库之MySQL Replace函数 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
本文主要向大家介绍了MySQL数据库之MySQL Replace函数 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
MySQL provides you with a useful string function called REPLACE to allow you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace text, which affects multiple records such as obsolete URL, spelling mistake…
The syntax of REPLACE function is as follows:
UPDATE tbl_name SET field_name = REPLACE(field_name,string_to_find,string_to_replace) WHERE conditions
Note that when searching for text to replace, MySQL uses case-sensitive match to perform search for string to be replaced.
For example, if you want to correct the spelling mistake in the products table in the sample database, you use the REPLACE function as follows:
UPDATE products SET productDescription = REPLACE(productDescription,'abuot','about')
The query will look at the column productDescription of the table products and find for all occurrences of spelling mistake ‘abuot’ and replace it by the correct word ‘about’.
It is very important to note that in the REPLACE function, the first parameter is the field name without quotes. If you put the quotes to the field name like ‘field_name’, the query will update the content of that column to ‘field_name’, which is causing unexpected data loss.
Currently REPLACE function does not support regular expression so if you need to replace a text string by a pattern you need to use MySQL user defined function (UDF) from external library, check it out here MySQL UDF with Regex
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号