MySQL 替换查询 [英] MySQL Replace query

问题描述

我有一个表格，我需要从特定字段中删除特定文本.此字段包含图像的完整 URL，我需要删除 URL 并保留图像文件名.

I have one table, and I need to remove a specific text from a specific field. This field contains a full URL of an image, I need to remove the URL and just keep the image filename.

所以:当前日期:字段名称:www.example.com/photo.jpg我想要做的是从该字段的所有条目中删除 www.example.com/.

So: Current date: fieldname: www.example.com/photo.jpg What I want to do is remove www.example.com/ from all of the entries for this field.

我知道如何使用搜索和替换功能，但我不知道如何完整保留部分数据.

I know how to use the search and replace function, but I don't know how to leave part of the data intact.

这是我使用过的，但无法修改它以使其按我想要的方式工作:

This is what I've used but can't modify it to make it work the way I want:

UPDATE table SET oc_upload1 = REPLACE(oc_upload1,'newtext') WHERE oc_upload1 LIKE "oldtext"

这可能吗?如果是这样，如何?谢谢！

Is this possible? If so, how? Thank you!

推荐答案

应该这样做:

UPDATE table
SET image = REPLACE(image, 'www.example.com/','')

但是，图像可能包含www.example.com/"作为图像文件名的一部分，因此更加安全并且仅替换第一次出现的 www.example.com

but, it's possible that image contains 'www.example.com/' as part of image file name so to be extra safe and replace only the first occurence of www.example.com

UPDATE table
SET image = SUBSTRING(image, LENGTH('www.example.com/') + 1)
WHERE image LIKE 'www.example.com/%'

但如果你真的，真的只想要文件名而不是文件路径，你也可以使用:

But if You really, really just want the file name and not path to the file You can also use:

UPDATE table
SET image = SUBSTRING_INDEX(image,'/',-1)

请注意，上述语句会将www.example.com/images/01/02/daisy.jpg"更改为daisy.jpg"，而不是images/01/02/daisy.jpg".它也不会更改图像中不包含/"的行.

Note that above statement will change 'www.example.com/images/01/02/daisy.jpg' to 'daisy.jpg', not 'images/01/02/daisy.jpg'. It also wont change rows that does not contain '/' in image.

这篇关于MySQL 替换查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！