I have a website I am taking over from a client, which I will fully rebuild at some point, but for now I need to work with what I have.
They have a SINGLE field in the database that is something like this.
```Vehicle```:```Audi A3 Sportback```Colour```:```Black```Comment```:```It's a pretty car, low miles: come see today.```
I asked the previous developer why he started and ended the field in 3 backticks and used 3 backticks to separate each record pair and then used 3 backticks and a colon and another 3 backticks as a separator between the key/value.
His answer was that the users were adding comments with slashes, commas, colons, etc and he needed something the user would more than likely never use to land up with an end result of
Vehicle: Audi A3 Sportback
Colour: Black
Comment: It's a pretty car, low miles: come see today.
Now I would like to split that strange string he has into 3 database fields
vehicle
colour
comment
Audi A3 Sportback
Black
It’s a pretty car, low miles: come see today.
I would like to do this using a database query, and then a multi update, where it takes the full odd string from the query and adds just the relevant part to the correct column in my table.
Normally I would just do this through Regex in a text editor and dump it all back to my database, however in this case I can’t and kind of need to do it like this.
I am hoping to use the formatters inside the update server action to extract the part i need for each field.
I was thinking about that too, but his old code is still writing new lines like that all day till I replace it, so maybe I could make a cron job to run each time the database updates or something.
Will give it a try, thank you Ken, appreciate the advice, I had skyped my MySQL guy @brian but he is probably busy, so I figured I best ask here, glad a few of you are MySQL proficient.
If the data is consistent in all the fields you can create a query, then add a repeat and inside the repeat add an insert step. In order to access the different properties use:
vehicle: {{str.split("```")[3]}}
color: {{str.split("```")[6]}}
comment: {{str.split("```")[9]}}
A quick example with setvalue steps:
results in:
So you can easily use these values in the insert step to put the data in each field
That’s how the string looks when you split it, and that’s where 3, 6 and 9 come from:
