MSSQL Database. IP주소 문자열 정렬하기

MSSQL Database. IP주소 문자열 정렬하기

SQL을 사용하다보면 필수적으로 항상 데이터 출력시 정렬을 해서 깔끔하게 보여줍니다.

날짜를 기준으로 정렬할 때가 있고, 또는 순번에 따라서 정렬을 할때가 있고, 알파벳 순, 한글 순으로 보여줍니다.

그러나 IP 주소와 같은 데이터를 정렬할 때에는 일반적인 정렬기준과 다르게 IP 대역별로 구분하여 그 크기를 비교해야 합니다.

가장 먼저 IP를 기존 방법으로 정렬할 때 나타나는 결과값을 확인해보겠습니다.

위의 사진은 Varchar 타입으로 저장 된 IP 주소를 단순히 ORDER BY sKey 로 정렬한 결과입니다. 

데이터를 확인해보면 마지막 4번째 대역인 데이터의 크기를 비교해서 1로 시작하는 값들을 먼저 정렬하고, 

그다음에 2로 시작하는 데이터들의 결과값들이 정렬되는것을 확인할 수 있습니다.

따라서 일반적으로 문자열을 정렬할 때 흔히들 쓰시는 LEN() 함수를 이용해서 정렬을 해보도록 하겠습니다.

실질적으로 LEN으로 변환을 하였을 때 어떤 데이터 값이 나오는지 확인하기 위해 LEN으로 변환한 컬럼값을 추가하였습니다.

쿼리 :    SELECT sKey, LEN(sKey) AS LEN

FROM DATABASE

ORDER BY LEN(sKey) 

위의 화면과 같이 LEN으로 변환했을 때의 값의 크기에 따라 정렬이 되는데, 10자리, 100자리의 순서는 정상적으로 정렬되지 않았음을 확인할 수 있습니다.

1자리 까지는 정상적으로 정렬이 되지만, 2자리 이상부터의 정렬이 되지 않는 이유는 비교를 하기 위한 기준(index)이 정확히 잡히지 않아서 이러한 결과가 나오는 것이라고 생각됩니다.

결론적으로 IP주소를 정렬하기 위해서는 일반적으로 예상할 수 있는 각 대역별 값을 비교하는 쿼리가 필요합니다.

가장 편리한 방법으로는 PARSENAME 함수를 사용하는 것입니다.

PARSENAME  함수는 데이터베이스 오브젝트 명칭들을 읽어올 수 있는 함수입니다. 

다만, 데이터베이스 오브젝트는 4부분으로 이루어져 있는데, 구분을 나눌때 사용한 것이 . 이기 때문에

.으로 구분되어있는 문자열을 4부분으로 나누어 가져올 수 있습니다.

PARSENAME 의 한계는 4개 이상의 문자열을 구분할 수 없습니다. 4개 이상의 문자열을 반환하도록

코딩하면 NULL값을 반환합니다.

----------------------------------------------------------------------------------------------------------------------------------------------

출처 : https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

PARSENAME ( 'object_name' , object_piece )   

Arguments

'object_name'
Is the name of the object for which to retrieve the specified object part. object_name is sysname. This parameter is an optionally-qualified object name. If all parts of the object name are qualified, this name can have four parts: the server name, the database name, the owner name, and the object name.

----------------------------------------------------------------------------------------------------------------------------------------------

위의 설명과 같이 PARSENAME 을 사용하여 각 구분된 데이터들 순서대로 정렬을 해보도록 하겠습니다.

SELECT sKey

FROM DATABASE

ORDER BY CAST(PARSENAME(sKey, 4) AS INT),

CAST(PARSENAME(sKey, 3) AS INT),

CAST(PARSENAME(sKey, 2) AS INT),

CAST(PARSENAME(sKey, 1) AS INT)

* 구분된 문자열을 INT 형으로 변경하여 숫자의 크기를 비교하기 위해 CAST 를 추가로 사용하였습니다.

위에 보시는 결과와 같이 PARSENAME 함수를 사용하면 올바르게 정렬이 되어 데이터가 표시되는것을 확인할 수 있습니다.

IP 주소뿐 아니라 "."으로 최대 4개까지 구분되는 문자열은 PARSENAME을 통해 편리하게 구분하여 정렬할 수 있습니다.