MySQL 删除部分字符串的 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12030143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL statement to remove part of a string
提问by Cameron
Possible Duplicate:
str_replace in SQL UPDATE?
How to remove part of string in mysql?
SQL Find & Replace part of a string
可能重复：
SQL UPDATE 中的 str_replace？
如何删除mysql中的部分字符串？
SQL 查找和替换字符串的一部分
I have a database table with a list of website urls e.g. http://website.com/and I want to remove all the http://and https://from them. Is their a simple SQL statement I could run on a column to remove it?
我有一个包含网站 url 列表的数据库表，例如http://website.com/，我想从中删除所有http://和https://。他们是我可以在列上运行以删除它的简单 SQL 语句吗？
I've had a search around, but I can't find what I need. I'm presuming I need to use both REPLACE and UPDATE but I'm struggling.
我四处寻找，但找不到我需要的东西。我假设我需要同时使用 REPLACE 和 UPDATE 但我很挣扎。
So far I have:
到目前为止，我有：
UPDATE list
SET website
WHERE website LIKE 'http://%';
Is that correct? I'm using MySQL and the table is list, and column is website and I want to remove the http://so a url like: http://website.com/becomes just: website.com
那是对的吗？我正在使用 MySQL，表格是列表，列是网站，我想删除http://这样的网址http://website.com/：website.com
EDIT: Is it also possible to remove a trailing slash as well?
编辑：是否也可以删除尾部斜杠？
回答by Jeremy Wiggins
Have a look at the REPLACEfunction. You'll need to use it twice to remove both http and https.
看看REPLACE功能。您需要使用它两次才能删除 http 和 https。
UPDATE list
SET website = REPLACE(REPLACE(website, 'https://', ''), 'http://', '')
WHERE website like 'https://%'
OR website like 'http://%'
To handle trailing slashes, you can use the RIGHT, LEFT, and LENGTHfunctions.
为了处理尾随斜线，您可以使用RIGHT，LEFT和LENGTH功能。
UPDATE list
SET website = LEFT(website, LENGTH(website) - 1)
WHERE RIGHT(website, 1) = '/'
Here is some documentation that you may find useful: MySQL string functions
以下是一些您可能会觉得有用的文档： MySQL 字符串函数
回答by Bridge
Why not just replace the string if it exists:
如果字符串存在，为什么不直接替换它：
UPDATE list
SET website = Replace(website, 'http://', '')
回答by Shehzad Bilal
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string', ‘replace found string with this string') where CONDITION
