How to derive specific elements from an attribute of a database and connect them to the primary key?

user_11462846 0
Viewed 36
CREATE TABLE `user` (
  `user_id` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `hobby` varchar(50) #optional, hobby is always written in hashtags 
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `user` (`user_id`, `name`, `hobby`)
('148723', 'max', 'random text #football'),
('578294', 'john', 'random text2'),
...

I want to create a table with only the ID and the hobby like so:

('148723', '#football')
...

Is there any way to do this in MySQL or do I have to use Python? Thanks in advance.

mysql python database
1 Answers

you have to create a new table es.user_and_hobby

CREATE TABLE `user_and_hobby` (
  `user_id` int NOT NULL,
  `hobby` varchar(50) 
);

then import data from another table es. user:

INSERT INTO `user_and_hobby` (`user_id`, `hobby`)
    SELECT user_id, concat('#',SUBSTRING_INDEX(hobby, "#", -1))
    FROM user
    WHERE hobby LIKE "%#%"

For more example about insert+select you can find here.

  • CONCAT add # before string (documentation).
  • SUBSTRING_INDEX select hashtag word without # (documentation).
  • LIKE remove hobby without hashtag

I suppose you have only one hashtag for hobby, if this is not the case, modify the question by entering all possible cases

user_106623530
Related