Menu
I would like to run over an entire table, populating the value of a newly created column with a substring of a value from another column.
Given a table structure not unlike the following:
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | varchar(150) | YES | | NULL | |
| domain | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
Which contains data resembling:
+----+-------------------------+--------+
| id | email | domain |
+----+-------------------------+--------+
| 1 | [email protected] | NULL |
| 2 | [email protected] | NULL |
| 3 | [email protected] | NULL |
| 4 | [email protected] | NULL |
| 5 | [email protected] | NULL |
| 6 | [email protected] | NULL |
+----+-------------------------+--------+
I would like to have a query to parse the domain portion of the email address, and put it in the domain column, to end up with a result like:
+----+-------------------------+-------------------+
| id | email | domain |
+----+-------------------------+-------------------+
| 1 | [email protected] | domain1.com |
| 2 | [email protected] | domain1.com |
| 3 | [email protected] | domain1.com |
| 4 | [email protected] | anotherdomain.com |
| 5 | [email protected] | anotherdomain.com |
| 6 | [email protected] | thethird.com |
+----+-------------------------+-------------------+
Currently, I am doing this outside of the MySQL engine with a shell script, but this is inefficient, and I'm sure there must be a better way to do it inside of the MySQL engine.
Efficiency is important here, as the tables I will be doing this on in production are tens or even hundreds of thousands of rows.
372
In MySQL, if you want to update a column with the value derived from some other column of the same table we can do so by using a SELF JOIN query and if you wish to modify the value derived from another column like maybe get a substring from the text or break the string using some delimiter, then we can use the ...
SELECT * FROM table1. FullName AS table1 JOIN table2.Name AS table2 WHERE table2.Name LIKE SUBSTRING(table1. FullName, 0, 10); this is returning null's being compared.
Certainly! You can use the SUBSTR operator to get a substring from a field. For example, to get bytes 3-7 of a field called MYNAME, use the expression SUBSTR(MYNAME,3,5). You can update part of a character field by concatenating the parts you want to stay intact with the new value.
In MySQL, we use SUBSTRING_INDEX() to split the string. It usually consists of three arguments i.e., string, delimiter, and position. The string value will be split based on the position.
You can use SUBSTRING_INDEX:
SELECT
id,
email,
SUBSTRING_INDEX(email, '@', -1) domain
FROM
yourtable
or this to update your data:
UPDATE yourtable
SET domain = SUBSTRING_INDEX(email, '@', -1)
Please see fiddle here.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
