SQL语句删除字符串的一部分 [英] SQL statement to remove part of a string

问题描述

可能重复:
在SQL UPDATE中替换str_replace?
如何在mysql中删除字符串的一部分?
SQL查找&替换字符串的一部分

Possible Duplicate:
str_replace in SQL UPDATE?
How to remove part of string in mysql?
SQL Find & Replace part of a string

我有一个包含网站网址列表的数据库表，例如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语句删除字符串的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！