SQL：有没有办法在SQL中进行批量字符串替换？

I have some data that needs to be rewritten in my db. I am trying to turn a full url into a relative url. So basically I need to go and change all the occurences of "https://www.website.com/uploads/images/1dhe5d5B56.jpg" to be more like "uploads/images/1dhe5d5B56.jpg".

我有一些数据需要在我的数据库中重写。我想把一个完整的网址变成一个相对网址。所以基本上我需要改变“https://www.website.com/uploads/images/1dhe5d5B56.jpg”的所有内容,更像是“uploads / images / 1dhe5d5B56.jpg”。

I know there is replace() but not sure if i can use that with an update instead of a select. Any ideas?

我知道有替换()但不确定我是否可以使用更新而不是选择。有任何想法吗?

3 个解决方案

#1

You can use replace in update. But be careful! If it is just a simple replace like in your question, then:

您可以在更新中使用replace。不过要小心!如果它只是你问题中的简单替换,那么:

Update [YourTable] 
set [ColumWithUrl] = Replace([ColumWithUrl], 'https://www.website.com/', '')

You can also specify which rows to update with WHERE. For all urls with this website:

您还可以使用WHERE指定要更新的行。对于本网站的所有网址:

Update [YourTable] 
set [ColumWithUrl] = Replace([ColumWithUrl], 'https://www.website.com/', '')
where [ColumWithUrl] like 'https://www.website.com/%'

Or only for image urls:

或仅适用于图片网址:

Update [YourTable] 
set [ColumWithUrl] = Replace([ColumWithUrl], 'https://www.website.com/', '')
where [ColumWithUrl] like 'https://www.website.com/uploads/images/%'

#2

Yes you can use REPLACE with UPDATE:

是的,您可以使用REPLATE更新:

UPDATE tab
SET col = REPLACE(REPLACE(col,'https://www.website.com/uploads/images','|place|')
           ,'|place|', 'uploads/images')
WHERE col LIKE '%https://www.website.com/uploads/images/%';

Rextester Demo

Standard Replace([ColumWithUrl], 'https://www.website.com/', '') could replace too many values like https://www.website.com/index.html.

标准替换([ColumWithUrl],'https://www.website.com/','')可以替换太多的值,如https://www.website.com/index.html。

For:

Index: https://www.website.com/index.html 
https://www.website.com/uploads/images/1dhe5d5B56.jpg 
and second https://www.website.com/uploads/images/xyz.jpg

=> 
Index: https://www.website.com/index.html 
uploads/images/1dhe5d5B56.jpg 
and second uploads/images/xyz.jpg

#3

substring() from the first '/' after the first '//'. Use charindex() to get their positions.

substring()来自第一个'/'之后的第一个'/'。使用charindex()来获取他们的位置。

substring(url,
          charindex('/',
                    url,
                    charindex('//',
                              url)
                    + 2),
          len(url)
          -
          charindex('/',
                    url,
                    charindex('//',
                              url)
                    + 2)
          + 1)

Or use replace() to remove 'http://' and 'https://' (if they are only at the beginning, which should be the case for sane URLs) and go for the first '/'.

或者使用replace()删除'http://'和'https://'(如果它们仅在开头,对于理智的URL应该是这种情况)并转到第一个'/'。

substring(replace(replace(url,
                          'http://',
                          ''),
                  'https://',
                  ''),
          charindex('/',
                    replace(replace(url,
                                    'http://',
                                    ''),
                    'https://',
                    '')),
          len(replace(replace(url,
                              'http://',
                              ''),
                      'https://',
                      ''))
          -
          charindex('/',
                    replace(replace(url,
                                    'http://',
                                    ''),
                    'https://',
                    ''))
          +
          1)

SQL Fiddle

Edit:

Hmm, I may have missed, that the URLs might be embedded in other text? That wasn't clear to me from the question. If that is the case my solution is only of partial use of course, if any. Maybe clarify if the URLs are embedded or not.

嗯,我可能错过了,URL可能嵌入在其他文本中?我不清楚这个问题。如果是这种情况,我的解决方案当然只是部分使用,如果有的话。也许澄清URL是否嵌入。

#1