Fetch domain name by passing name in MySQL?

To fetch domain name by passing name in MySQL, you can use substring_index(). Let us first create a table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable</span>
<span class="pun">   (</span>
<span class="typ">   UserId</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL AUTO_INCREMENT PRIMARY KEY</span><span class="pun">,</span>
<span class="typ">   UserMailId</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">200</span><span class="pun">)</span>
<span class="pun">   );</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.77</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">UserMailId</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'John9989@facebook.com'</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.18</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">UserMailId</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'983773CS@yahoo.com'</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.23</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">(</span><span class="typ">UserMailId</span><span class="pun">)</span><span class="pln"> values</span><span class="pun">(</span><span class="str">'Chris95@gmail.com'</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.20</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statement −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

This will produce the following output −

+--------+-----------------------+
| UserId | UserMailId            |
+--------+-----------------------+
|      1 | John9989@facebook.com |
|      2 | 983773CS@yahoo.com    |
|      3 | Chris95@gmail.com     |
+--------+-----------------------+
3 rows in set (0.00 sec)

Following is the query to fetch domain name by passing name in MySQL.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span>
<span class="typ">   UserId</span><span class="pun">,</span><span class="typ">UserMailId</span><span class="pun">,</span>
<span class="pln">   substring_index</span><span class="pun">(</span><span class="pln">substring_index</span><span class="pun">(</span><span class="typ">UserMailId</span><span class="pun">,</span><span class="pln"> </span><span class="str">'@'</span><span class="pun">,</span><span class="pln"> </span><span class="pun">-</span><span class="lit">1</span><span class="pun">),</span><span class="pln"> </span><span class="str">'.'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">`Domain_Name`</span>
<span class="kwd">   from</span><span class="pln"> </span><span class="typ">DemoTable</span><span class="pun">;</span>

This will produce the following output. Here, domain name is fetched −

+--------+-----------------------+-------------+
| UserId | UserMailId            | Domain_Name |
+--------+-----------------------+-------------+
|      1 | John9989@facebook.com | facebook    |
|      2 | 983773CS@yahoo.com    | yahoo       |
|      3 | Chris95@gmail.com     | gmail       |
+--------+-----------------------+-------------+
3 rows in set (0.01 sec)