0

I made a mistake in a app I'm developing and it turns out that there are some text that have multiples times double quotes escaped, something like this

We will begin by constructing the front and sides of the bar. We will first create frames for both the front and sides using 2\\\\\\\\" x 4\\\\\\\\"s and will then secure particle board panels over the frames. The bar in this project is 42\\\\\\\\" tall by 60\\\\\\\\" wide by 40\\\\\\\\" deep. You will be drilling several screws in this project. To make this process easier- first drill pilot holes for the screws in your frame using your Dremel Rotary Tool and a 150 1/8\\\\\\\\" Drill Bit.

I'm looking for a way to do an update in the DB that remove all those slashes quickly, so far my thinking is to use a regular expression, something like

for each($records as $record){
  $record->description = preg_replace(*/some patter here/*, $record->description);
  $record->save();
}

However I'm having a hard time looking for the correct pattern, so maybe someone can please help with this, or if there is a easier or better way to updates these records, I really appreciate any help!

Patrick Q
  • 6,373
  • 2
  • 25
  • 34
goseta
  • 770
  • 1
  • 7
  • 26
  • 2
    `update yourfield set field=replace(yourfield, '\\\\', '\\') where yourfield like '\\\\'`, basically. just do a repeated string update until no records are matched. – Marc B Nov 26 '14 at 19:26
  • If the number of backslashes is the same in every case, you can just write a pattern to change that into \". If they're different, replace \\" with \" multiple times. The pattern would be /\\\\"/. That's two escaped backslashes and a quote. – Bob Brown Nov 26 '14 at 19:27

2 Answers2

1

You should almost never be storing backslash-escaped content in the database. To clean these up you probably want something like:

preg_replace( "/\\\\{2,}/", "", $record->description );

That will replace sequences of 2 or more backslashes with the empty string. If necessary you can make it more specific -- so that it would only match those sequences of backslashes followed by a double-quote character:

preg_replace( '/\\\\{2,}"/', '"', $record->description );

That's if you want to do it via PHP. Your database engine may have a built-in regular expression replace function that would allow you to perform the update just using a query, and would likely be higher performance, if that matters. The pattern would likely be the same.

I just noticed your MySQL tag. Apparently MySQL does not have a built-in regex replace function. See How to do a regular expression replace in MySQL?

My suggestion is to get rid of the backslash escapes completely and then escape as necessary on output, unless there's a good reason to store the content escaped. You could of course alter the replacement string to replace those sequences with a single backslash or whatever you want.

Community
  • 1
  • 1
JMM
  • 26,019
  • 3
  • 50
  • 55
1
preg_replace("/([\\]){2,}/", "\\", $record->description);

should do the trick.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68