Database Reference
In-Depth Information
+--------------------+----------+----------+-----------+
| name | leftmost | middle | rightmost |
+--------------------+----------+----------+-----------+
| svn.php.net | svn | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | mysql | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+--------------------+----------+----------+-----------+
Notice the output for the
mysql.com
row; it has
mysql
for the value of the
leftmost
column, where it should have an empty string. The segment-extraction expressions
work by pulling off the rightmost
n
segments, and then returning the leftmost segment
of the result. The source of the problem for
mysql.com
is that if there aren't
n
segments,
the expression simply returns the leftmost segment of however many there are. To fix
this problem, add a sufficient number of periods at the beginning of the hostname values
to guarantee that they have the requisite number of segments:
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 |
+--------------------+----------+----------+-----------+
| svn.php.net | svn | 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 |
+--------------------+----------+----------+-----------+
That's pretty ugly. But the expressions do serve to extract the substrings that are needed
for sorting hostname values correctly in right-to-left fashion:
mysql>
SELECT name FROM hostname
->
ORDER BY
->
SUBSTRING_INDEX(name,'.',-1),
->
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1),
->
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1);
+--------------------+
| name |
+--------------------+
| www.kitebird.com |
| mysql.com |
| lists.mysql.com |
| svn.php.net |
