예제 데이터 준비
'ipaddress'라는 테이블에 'IP' 컬럼에 IP 주소들이 저장되어 있다고 가정해 보겠습니다.
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)
SUBSTRING_INDEX() 함수로 옥텟 분리하기
이제 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 |
+-----------------+----------+----------+----------+----------+
4 rows in set (0.05 sec)쿼리 동작 원리
SUBSTRING_INDEX(문자열, 구분자, 위치) 함수는 지정한 구분자가 '위치'번째로 나타나기 전까지의 문자열을 반환합니다. 위치 값이 양수이면 왼쪽에서, 음수이면 오른쪽에서부터 계산한다는 점이 핵심입니다.
- 첫 번째 옥텟: SUBSTRING_INDEX(ip, '.', 1) — 왼쪽에서 첫 번째 마침표 앞부분을 그대로 추출합니다.
- 두 번째 옥텟: SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 2), '.', -1) — 먼저 왼쪽에서 두 번째 마침표까지 잘라낸 뒤, 거기서 오른쪽 첫 번째 부분만 다시 추출합니다.
- 세 번째 옥텟: SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) — 오른쪽에서 두 번째 마침표까지 자른 후 왼쪽 첫 번째 부분을 가져옵니다.
- 네 번째 옥텟: SUBSTRING_INDEX(ip, '.', -1) — 오른쪽에서 첫 번째 마침표 뒷부분을 추출합니다.
이처럼 중첩된 SUBSTRING_INDEX() 호출을 조합하면 별도의 복잡한 문자열 처리 로직 없이도 IP 주소의 각 옥텟을 간단하고 정확하게 분리할 수 있습니다.