of segments
mysql>
mysql> CREATE TABLE hostname
-> (
-> name VARCHAR(64)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO hostname (name)
-> VALUES
-> ('cvs.php.net'),
-> ('dbi.perl.org'),
-> ('lists.mysql.com'),
-> ('mysql.com'),
-> ('jakarta.apache.org'),
-> ('www.kitebird.com')
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT name,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1)
-> AS leftmost,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1)
-> AS middle,
-> SUBSTRING_INDEX(name,'.',-1) AS rightmost
-> FROM hostname;
+--------------------+----------+----------+-----------+
| name | leftmost | middle | rightmost |
+--------------------+----------+----------+-----------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+--------------------+----------+----------+-----------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table hostname;
Query OK, 0 rows affected (0.00 sec)