CODEWITHSUNDEEP
mysql
Jmichelsen
Jmichelsen

Reputation: 257

MySQL Replace quotes with brackets

Hey everyone, I've got a problem and Google hasn't yielded the help I need so I'm turning to the experts ;)

I've got a MySQL database and one field is used for user input. Some users feel the need to quote their entry like so: "cheese press" and I need to change all entries in that column that have "quotes" to brackets like so: (cheese press)

I've tried these:

UPDATE `inputColumn` SET `userInput` = REPLACE(`userInput`, '"', '(');

The results were obvious the second I hit enter: (cheese press( I did this on a test field, no worries.

Then I tried:

UPDATE `inputColumn` SET `userInput` = REPLACE(`userInput`, '"%', '(');

That didn't do anything at all and neither did 

UPDATE `inputColumn` SET `userInput` = REPLACE(`userInput`, '"%"', '(%)');

I really don't want to go through and update 3000 records manually :) any ideas?

Upvotes: 1

Views: 1304

Answers (2)

Karl
Karl

Reputation: 3372

Its a bit of a cludge but if you can trust that there is a space before the first quote and a space after the second then try

UPDATE `inputColumn` SET `userInput` = REPLACE(`userInput`, ' "', ' (');

and 

UPDATE `inputColumn` SET `userInput` = REPLACE(`userInput`, '" ', ')');

Note the spaces and different brackets.

Upvotes: 1

Johan Soderberg
Johan Soderberg

Reputation: 2740

Perhaps something like this.

UPDATE `inputColumn`
SET    `userInput` = CONCAT('(', SUBSTR(`userInput`, 2, LENGTH(`userInput`)-2), ')')
WHERE  `userInput` LIKE '"%"';

Upvotes: 2

Related Questions