问题描述
我有一个包含网站网址列表的数据库表,例如http://website.com/
,我想从中删除所有的http://
和https://
.是他们可以在列上运行以删除它的简单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?
我已经搜索了一下,但是找不到我需要的东西.我想我需要同时使用REPLACE和UPDATE,但是我很挣扎.
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.
到目前为止,我有:
UPDATE list
SET website
WHERE website LIKE 'http://%';
那是正确的吗?我正在使用MySQL,表是列表,列是网站,我想删除http://
,所以像这样的URL:http://website.com/
变为:website.com
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
是否也可以删除斜杠?
推荐答案
看看REPLACE
函数.您需要使用它两次才能同时删除http和https.
Have a look at the REPLACE
function. You'll need to use it twice to remove both http and https.
UPDATE list
SET website = REPLACE(REPLACE(website, 'https://', ''), 'http://', '')
WHERE website like 'https://%'
OR website like 'http://%'
要处理斜线,可以使用RIGHT
,LEFT
和LENGTH
函数.
To handle trailing slashes, you can use the RIGHT
, LEFT
, and LENGTH
functions.
UPDATE list
SET website = LEFT(website, LENGTH(website) - 1)
WHERE RIGHT(website, 1) = '/'
以下是一些可能对您有用的文档: MySQL字符串函数
Here is some documentation that you may find useful:MySQL string functions
这篇关于SQL语句删除字符串的一部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!