MySQL SUBSTRING_INDEX() ফাংশন দিয়ে IP অ্যাড্রেস চার অক্টেটে ভাগ করা

ধরুন ipaddress নামক একটি টেবিল আছে যেইতে IP অ্যাড্রেস থাকে:

mysql> SELECT * FROM ipaddress;
+-----------------+
| ip              |
+-----------------+
| 192.128.0.5     |
| 255.255.255.255 |
| 192.0.255.255   |
| 192.0.1.5       |
+-----------------+

SUBSTRING_INDEX() ফাংশন ব্যবহার করে IP অ্যাড্রেস চার অক্টেটে ভাগ:

mysql> SELECT IP,
    SUBSTRING_INDEX(ip, '.', 1) AS '1st Part',
    SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) AS '2nd Part',
    SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) AS '3rd Part',
    SUBSTRING_INDEX(ip, '.', -1) AS '4th Part'
FROM ipaddress;

ফলাফল:

+-----------------+----------+----------+----------+----------+
| IP              | 1st Part | 2nd Part | 3rd Part | 4th Part |
+-----------------+----------+----------+----------+----------+
| 192.128.0.5     | 192      | 128      | 0        | 5        |
| 255.255.255.255 | 255      | 255      | 255      | 255      |
| 192.0.255.255   | 192      | 0        | 255      | 255      |
| 192.0.1.5       | 192      | 0        | 1        | 5        |
+-----------------+----------+----------+----------+----------+