SQL replacing part of a url string

Question

I want to replace characters in a part of a url.

my url

www.website.com/images/some-image-in-the-folder.jpg

For example I will change all "o"s to "1" comes after the

www.website.com/images/ (always the same)

Result will be.

www.website.com/images/s1me-image-in-the-f1lder.jpg

MY data is like: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas nibh est, suscipit vel vestibulum in, bibendum eget erat. Sed condimentum auctor vulputate. Ut malesuada massa eget libero lobortis sodales. img src="www.website.com/images/some-image-in-the-folder.jpg" Sed eleifend lobortis nisl sit amet faucibus. Morbi eu nunc eget sapien pellentesque convallis eget at magna. Vivamus convallis sem at ante sodales vehicula. Morbi tempus tortor non leo fermentum scelerisque.

How do I do that?

MySQL 5.0.95 Apache/2.2.3 phpMyAdmin 3.5.7

Many thanks. Im new at this :/

Answer 1

Try this, mysql

SET @url := 'www.website.com/images/some-image-in-the-folder.jpg';
SET @notreplace := 'www.website.com/images/';

SELECT CONCAT(@notreplace , REPLACE(MID(@url,LENGTH(@notreplace)+1,
LENGTH(@url)-LENGTH(@notreplace)),'o','1') )

• SQLFiddle Demo

SQL

DECLARE @url VARCHAR(1000)
DECLARE @notreplace VARCHAR(1000)

SET @url = 'www.website.com/images/some-image-in-the-folder.jpg'
SET @notreplace = 'www.website.com/images/'

SELECT @notreplace + REPLACE(SUBSTRING(@url,LEN(@notreplace)+1,LEN(@url)-LEN(@notreplace)),'o','1') 

Answer 2

(This solution works most dynamic, by replacing every "o" after the last "/" - no matter what the URL is)

The problem is, that just using REPLACE() would replace all occurrences of "o" - so you would have to split the string and then replace. Let's start with the split:

SELECT SUBSTRING(
  'www.website.com/images/some-image-in-the-folder.jpg',
  1,
  CHAR_LENGTH('www.website.com/images/some-image-in-the-folder.jpg') - LOCATE('/', REVERSE('www.website.com/images/some-image-in-the-folder.jpg')) + 1
);

This gives www.website.com/images/

Next, grab the right content and replace the string:

SELECT REPLACE(
  SUBSTRING_INDEX('www.website.com/images/some-image-in-the-folder.jpg', '/', -1),
  'o',
  '1'
);

This gives `'s1me-image-in-the-f1lder.jpg'

Finally, concat both:

SELECT
  CONCAT(
    SUBSTRING(
      'www.website.com/images/some-image-in-the-folder.jpg',
       1,
       CHAR_LENGTH('www.website.com/images/some-image-in-the-folder.jpg') - LOCATE('/', REVERSE('www.website.com/images/some-image-in-the-folder.jpg')) + 1
    )
  ,
    REPLACE(
      SUBSTRING_INDEX('www.website.com/images/some-image-in-the-folder.jpg', '/', -1),
      'o',
      '1'
    )
  )
;

Voila: www.website.com/images/s1me-image-in-the-f1lder.jpg :)

Notably, all used functions are bianry safe.

Answer 3

This solution will allow you to search based on the value in the database (You won't need to supply a static string):

First: Use substring_index to get the section of the string that comes after www.website.com/images/

Second: Replace all instances of o returned from the first step with 1

Third: Update the table with the final result

UPDATE table 
SET    yourcolumn = (SELECT Replace((SELECT 
                                   Substring_index((SELECT yourcolumn 
                                             FROM   yourtable), 
                                                'www.website.com/images/', 1 
                                                        )), 'o', '1')); 

Answer 4

This one worked for me:

update my_table set my_colum = replace(my_colum, "o", "1") 
WHERE post_content REGEXP "http://www\.website\.com/images/.*[o]+.*";