0

Using MySQLAdmin. Moved data from Windows server and trying to replace case in urls but not finding the matches. Need slashes as I don't want to replace text in anything but the urls (in post table). I think the %20 are the problem somwhow?

UPDATE table_name SET field = replace(field, '/user%20name/', '/User%20Name/')

The actual string is more like: 

https://www.example.com/forum/uploads/user%20name/GFCI%20Stds%20Rev%202006%20.pdf
TIJ
  • 1
  • 2
  • What is the current output from that replacement, and what result do you actually want? Then there is the question of what encoded URLs are doing in your database. – Tim Biegeleisen May 01 '17 at 14:22
  • `%` is a wildcard character in MySQL, so I suspect you may need to excape it. – ceejayoz May 01 '17 at 14:22
  • 1
    @ceejayoz I think `%` only has a wildcard meaning when used in `LIKE`. In a simple replacement, it should behave like any other character. – Tim Biegeleisen May 01 '17 at 14:23
  • Doesn't find any matches... code does nothing. – TIJ May 01 '17 at 14:57
  • I made a big mistake without noticing and I can't seem to edit my original question or tags. The problem is in phpMyAdmin and NOT MySQLAdmin. Very sorry for confusion I caused by that slip up. It still doesn't work. – TIJ May 02 '17 at 19:25

3 Answers3

0

In a case you are using MariaDB you have REGEXP_REPLACE() function.

But best approach is to dump the table into the file. Open it in a Notepad ++

and run regex replace like specified on a pic: Pattern is: (https:[\/\w\s\.]+uploads/)(\w+)\%20(\w+)((\/.*)+) Replace with: $1\u$2\%20\u$3$4

enter image description here

Then import the table again Hope this help

volkinc
  • 2,143
  • 1
  • 15
  • 19
0

If its MariaDB, you can do the following:

UPDATE table_name SET field = REGEXP_REPLACE(field, '\/user%20name\/', '\/User%20Name\/');
Srihari Karanth
  • 2,067
  • 2
  • 24
  • 34
0

First, please check, what is actually stored in the database: %20 is a html-entity which represents a whitespace. Usually, when you are storing this inside the database, it will be represented as an actual whitespace (converted before you store it) -> Hence your replace doesn't match the actual data.

The second option that might be possible - depending on what you want to do: You are seeing the URL containing %20, therefore you created your database records (which you would like to fetch) with that additional %20 - And when you now try to query your results based on the actual url, the %20 is replaced with an "actual" whitespace (before your query) and hence it doesn't match your stored data.

dognose
  • 20,360
  • 9
  • 61
  • 107
  • I look at my local copy that I published using MySqlWorkbench and pasted text into notepad++ and %20 is there. – TIJ May 01 '17 at 17:50
  • There are quotes around the strings? src="https://www.example.com/forum/uploads/User%20Name/tn/tn_20174212624_IMG_0393.jpg" – TIJ May 01 '17 at 17:54