How to insert values into one column based on values of another column for each row?

user_8008906 0
Viewed 20
# table schema
CREATE TABLE `ref_str` (
  `ref_id` int(11) DEFAULT NULL,
  `ref_str` varchar(4096) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

This is current data:

+--------+--------------------+
| ref_id | ref_str            |
+--------+--------------------+
|   NULL | [5] www.NCCN.com   |
|   NULL | [4] www.nhc.gov.cn |
|   NULL | [14] www.hc.gov.cn |
|   NULL | [146] someh.gov.cn |
+--------+--------------------+

And I want to iteratively insert into ref_id column the int value inside [] of ref_str of same row.

The result should look like:

+--------+--------------------+
| ref_id | ref_str            |
+--------+--------------------+
|    5   | [5] www.NCCN.com   |
|    4   | [4] www.nhc.gov.cn |
|   14   | [14] www.hc.gov.cn |
|  146   | [146] someh.gov.cn |
+--------+--------------------+

I'm using MySQL 5.7.32.

Thanks.

stored-procedures mysql
2 Answers

You could use SUBSTRING_INDEX here, for example:

SELECT
    SUBSTRING(SUBSTRING_INDEX(ref_str, ']', 1), 2) AS ref_id,
    ref_str
FROM ref_str;

screen capture from demo link below

Demo

user_18632290 
UPDATE ref_str
SET ref_id = 0 + TRIM(LEADING '[' FROM ref_str);

fiddle

Firstly we remove leading bracket. Then we convert the value implicitly to a number - non-numeric tail is simply cut off.

user_101387340
Related