varchar 타입으로 저장한 IP주소의 정렬

IP주소를 varchar 타입으로 저장하고 있다면, 지난 번 IP주소를 저장하는 방법에 대한 포스트에서 언급했듯이 정렬에 트릭이 필요하다.

즉, ORDER BY 절 만으로는 원하는 결과가 나오지 않는다는 뜻.

이미 널리 알려진 방법이긴 하지만...
몇 년전 SQL Server Magazine에 게재됐었고, 나름 유용하게 사용했었던 방법을 소개한다.

단, 이 방법 역시 인덱스를 활용하지 못하는 면에서 성능 상 문제를 야기할 수 있으니...
정렬할 대상이 되는 집합의 크기를 충분히 줄여준 상태에서 사용해야한다.

CREATE TABLE dbo.IPAddresses (IPAddress varchar(15) NOT NULL);

GO

 

INSERT dbo.IPAddresses (IPAddress)

SELECT '123.123.123.0'

UNION ALL

SELECT '123.123.123.1'

UNION ALL

SELECT '123.123.123.2'

UNION ALL

SELECT '123.123.123.3'

UNION ALL

SELECT '123.123.123.4'

UNION ALL

SELECT '123.123.123.10'

UNION ALL

SELECT '123.123.123.11'

UNION ALL

SELECT '123.123.123.12'

UNION ALL

SELECT '123.123.123.13'

UNION ALL

SELECT '123.123.123.14';

GO

 

SELECT IPAddress FROM dbo.IPAddresses ORDER BY IPAddress;

GO

- 단순히 IPAddress열로 ORDER BY 했을 때의 결과. 보통 이런 결과를 원하진 않을 듯...

 

SELECT IPAddress

FROM dbo.IPAddresses

ORDER BY CAST(PARSENAME(IPAddress, 4) AS int)

       , CAST(PARSENAME(IPAddress, 3) AS int)

       , CAST(PARSENAME(IPAddress, 2) AS int)

       , CAST(PARSENAME(IPAddress, 1) AS int);

GO

- PARSENAME 함수를 사용한 방법으로, 아래와 같은 원하는 결과를 얻을 수 있다.
- 본래 PARSENAME함수는 SQL서버의 네부분 이름(서버명.데이터베이스명.스키마명.객체명) 중 원하는 부분의 이름을 파싱해 반환해주는 함수인데, IPv4의 주소 표현 방법과 SQL 서버 네부분 이름의 형태가 동일하기 때문에 사용할 수 있는 트릭이다.