Prepend a sufficient number of periods to the hostname values to guarantee that they have the requisite number

       
 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)

Related examples in the same category

1.	To return everything to the right or left of a given character, use SUBSTRING_INDEX(str,c,n).
2.	Get the second and fourth segments from the id values:

Prepend a sufficient number of periods to the hostname values to guarantee that they have the requisite number : SUBSTRING_INDEX « String « SQL / MySQL

Related examples in the same category