MySQL query – pieces of string with a specific substring in filed data. you can use an alias in “where” condition in the query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT ip, SUBSTRING_INDEX(ip,'.',1) AS part1, SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) AS part2, SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1) AS part3, SUBSTRING_INDEX(ip,'.',-1) AS part4 FROM log_file; +-----------------+-------+-------+-------+-------+ | ip | part1 | part2 | part3 | part4 | +-----------------+-------+-------+-------+-------+ | 1.2.3.4 | 1 | 2 | 3 | 4 | | 5.6.7.8 | 5 | 6 | 7 | 8 | | 255.255.255.255 | 255 | 255 | 255 | 255 | +-----------------+-------+-------+-------+-------+ 3 rows in set (0.01 sec) |
More Stories
Incorrect datetime value: ‘0000-00-00 00:00:00’ for column
MySQL String Functions
Compare comma-separated field with single string