MySQL通配符替换

问题描述

我需要MySQL查询方面的帮助.
是否可以对字符串进行通配符替换，例如下面的示例?

Hi i need help with a MySQL query.
Is it possible to do some wildcard replace for a string like the example below?

字符串:           thinsp; thinsp; thinsp; thinsp; thinsp;         ; example.com/folderone /some/path/to/file.pdf
字符串:           thinsp; thinsp; thinsp; thinsp; thinsp; thinsp; thinsp; thinsp; > example.com/foldertwo /some/path/to/file.pdf
替换: newsite.com /some/path/to/file.pdf

String:                        example.com/folderone/some/path/to/file.pdf
String:                        example.com/foldertwo/some/path/to/file.pdf
Replace: newsite.com/some/path/to/file.pdf

newsite.com/some/path/to/file.pdf
newsite.com/some/path/to/file.pdf

newsite.com/some/path/to/file.pdf
newsite.com/some/path/to/file.pdf

要删除文件夹并更改域，但要保留路径.在这种情况下，每个文件夹都有一个不同名称和一个不同长度.

To remove the folder and change the domain but keep the path. In this case each folder have a different name with a different length.

类似:

update TABLE set COLUMN = replace(COLUMN, 'example.com/%/', 'newsite.com/');

推荐答案

使用 SUBSTRING_INDEX :

UPDATE table1
   SET column1 = REPLACE(
         column1,
         SUBSTRING_INDEX(column1, '/', 2),
         'newsite.com'
       )
 WHERE column1 LIKE 'example.com/%/'

这应该符合您的子文件夹结构.

This should honour your subfolder structure.

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