MySql - Adding strings to column values from other column within the same table

Question

I have table which include 2 column: title and param, the values are like the following:

-title: Teaching

-params:

{   "ufield926":"34",
    "ufield927":"Sud",
    "ufield928":"Ara",
    "ufield929":"Mecca",
    "ufield930":"1\/1\/1983",
    "ufield933":"011",
    "ufield934":"Mub",
    "ufield943":"SU\/HI\/14",
    "ufield944":"Average",
    "ufield946":"Female"
}

I want to extract the code after "ufield943": which is SU/HI/14 only and concatenate it with the value in title column to be like the following:

--> Teaching (SU/HI/14)

Here is the query I tryingI have tried:

update tbl set title = CONCAT_WS(title,' ', '(' , (select *, substring( params, locate('ufield943', params) + 12, locate('ufield944', params) - locate('ufield943', params) - 15 )),')') from tbl;

I get the following error "ERR_PARSE_ERROR" every time I run the code.

Now maybe you see why comma delimited lists are such a bad idea in a database column — RiggsFolly
– RiggsFolly, Commented Jun 20, 2019 at 15:16
I think you could use a script like php to do that (if you have a lot of rows) — Med.ZAIRI
– Med.ZAIRI, Commented Jun 20, 2019 at 15:22
The real question is why you try to parse/extract from JSON data without using the native JSON functions which MySQL supports — Raymond Nijland
– Raymond Nijland, Commented Jun 20, 2019 at 15:24
Actually I have to make this update from phpmyadmin, that's why I am using mysql not php. Is there any ideas how to make the query work ? — Abbas Soliman
– Abbas Soliman, Commented Jun 20, 2019 at 15:29

aLamp · Accepted Answer · 2022-07-04 14:03:18Z

1

Export the table row/s to a csv, upload to google sheets and use the tool 'split text to column' and cleanup the csv.

Import the CSV back to the mysql table.

Hope it helps.

answered Jul 4, 2022 at 14:03

aLamp

772 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rohit Gupta Over a year ago

I agree, the OP should fix the data once.

Collectives™ on Stack Overflow

MySql - Adding strings to column values from other column within the same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related