CODEWITHSUNDEEP
mysqlsql-update
user6631314
user6631314

Reputation: 1960

Update Column by Parsing String from Other Column in MYSQL

I would like to create a new column in a MYSQL table based on the string values in an existing column.

My strategy is to first create an empty column and then update the values in the new column based on values in the existing column. However, I am stumbling on how to parse the string in order to extract the correct values.

The string is of the form 1.1.25. I want to extract the value before the first period and the value between the two periods and put these in new columns.

mytable
id|actsceneline|text
1 |1.1.1       |How are you.
1 |1.1.2       |Not bad. You?

To create the new empty column

ALTER TABLE mytable
ADD COLUMN act VARCHAR(6) NOT NULL,
ADD COLUMN scene VARCHAR(6) NOT NULL

To change the values in the new columns, I imagine I would do something like:

UPDATE mytable SET act = '1',scene = 1

And then use MYSQL string functions such as instr or substr or regex to extract the values and update the new columns as in.

UPDATE mytable SET act = 
SELECT SUBSTR(actsceneline, 1, LOCATE('.', text)) FROM mytable

However, I'm struggling with how to extract the values from the string.

Thanks for any suggestions.

Upvotes: 0

Views: 618

Answers (2)

Shreekrishna Shankhwar
Shreekrishna Shankhwar

Reputation: 29

Best way to create a select and what you want to update. create a new table from your existing table. "create table destinationtablename select * from sourcetable;" then work on your destinationtablename. All work finished then check twice before update to original table or you can also take backup of your data by creating new table.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

Try using SUBSTRING_INDEX():

UPDATE mytable 
SET act = SUBSTRING_INDEX(actsceneline, '.', 1), 
    scene = SUBSTRING_INDEX(SUBSTRING_INDEX(actsceneline, '.', 2), '.', -1);

Result given your data:

mysql> select * from mytable;
+----+--------------+---------------+-----+-------+
| id | actsceneline | text          | act | scene |
+----+--------------+---------------+-----+-------+
|  1 | 1.1.1        | How are you.  | 1   | 1     |
|  2 | 1.1.2        | Not bad. You? | 1   | 1     |
+----+--------------+---------------+-----+-------+

Upvotes: 2

Related Questions