Using regex to increment part of a string within MYSQL?

Regexs baffle me. I have a situation where I have JSONs stored in a db. I need to go into the JSON string, find a part of it, and increment it.

In this case, the phrase I'm searching for is "selectedLib":"x" where x is always a number (but a number that has, in this case, been wrapped in quotes and so is technically a string).

So, "selectedLib":"1", needs to become "selectedLib":"2", "selectedLib":"23" needs to become "selectedLib":"24", and so on.

This is a real head-scratcher...

asked Sep 15, 2014 at 21:43

Benjamin Allison

2,1543 gold badges31 silver badges57 bronze badges

MySQL can only use regexp to match strings, it doesn't have regexp replace.
– Barmar
Commented Sep 15, 2014 at 21:44
2

I guess this should teach you not to store JSON in the database if you need to use the value. You should normalize your schema and put the data in a real table.
– Barmar
Commented Sep 15, 2014 at 21:45
I know I know. The problem is that these JSONs are essentially a document format that is used by a JS front-end (Knockout). Normalizing something like that will really kill performance and add bloat.
– Benjamin Allison
Commented Sep 15, 2014 at 21:49
1

In which case, you'll most likely need to pull the data out, increment the value, and then re-store
– M Sost
Commented Sep 15, 2014 at 21:51
"add bloat", don't think you can call something bloat if it makes your database design much easier to manipulate. "kill performance", I think you are killing performance with JSON now that you will need to pull all data, increment it, and then update all data (rather than just UPDATE table SET column+=1).
– Sam
Commented Sep 15, 2014 at 21:53

| Show 4 more comments

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.

Collectives™ on Stack Overflow

Using regex to increment part of a string within MYSQL? [duplicate]

0

Linked

Hot Network Questions