Example of MySQL SUBSTRING_INDEX()

Example of MySQL SUBSTRING_INDEX()

 

특정 구분자로 문자열을 분리해 내야할때 필요하다.

 

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 |
+-----------------+-------+-------+-------+-------+
| 127.0.0.1       | 127   | 0     | 0     | 1     |
| 192.128.0.15    | 192   | 128   | 0     | 15    |
| 255.255.255.255 | 255   | 255   | 255   | 255   |
+-----------------+-------+-------+-------+-------+
3 rows in set (0.00 sec)

- See more at: http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php#sthash.UFdjNs2M.dpuf

 

 

출처 : http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php