Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we split an IP Address into four respective octets by using MySQL SUBSTRING_INDEX() function?
Suppose we have a table named ‘ipaddress’ which contains the IP addresses as its values in column ‘IP’ as follows −
mysql> Select * from ipaddress; +-----------------+ | ip | +-----------------+ | 192.128.0.5 | | 255.255.255.255 | | 192.0.255.255 | | 192.0.1.5 | +-----------------+ 4 rows in set (0.10 sec)
Now with the help of SUBSTRING_INDEX() function in the following query, we can divide the IP address in four octets −
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 | +-----------------+----------+----------+----------+----------+ 4 rows in set (0.05 sec)
Updated on: 10-Feb-2020
525 Views
Advertisements