CODEWITHSUNDEEP
mysqlregexsearchreplace
justnajm
justnajm

Reputation: 4534

mysql regex vs multiple replace?

Regex is not available for replace but what about multiple replace behave as regex

UPDATE `usertree` SET branchIndex=REPLACE(branchIndex,'80',''),
branchIndex=REPLACE(branchIndex,',,',',') WHERE id=1

its working successfully, do it have drawbacks ? I get into trouble later

My new tree game has good number of technologies attached to it:

I am saving added branches in a column name branchIndex and it is a comma seprated string holding branch positions on tree.

But when user cuts a branch I need to remove that index from string, plus keep the string accurate. Also it would be happening more frequently by more then 2000 - 3000 users.

Upvotes: 1

Views: 406

Answers (2)

justnajm
justnajm

Reputation: 4534

These steps can be done in one run, breaking for more clear view:

SELECT REPLACE(CONCAT(',','80,138,149,145,120,180,280',','), ',80,', ',');

Better keep it like this:

SELECT TRIM(BOTH ',' FROM REPLACE(CONCAT(',','80,138,149,145,120,180,280',','), ',80,', ','));

Upvotes: 0

Barmar
Barmar

Reputation: 782130

If you want to do multiple replacements on the same column, you should write it as:

SET branchIndex = REPLACE(REPLACE(branchIndex, '80', ''), ',,', ',')

But instead of a comma-separated string, it would probably be better to put the branches into a separate table. Then you just add and delete rows from the table.

Upvotes: 1

Related Questions