[안드로이드] SQLite 테이블 조회시 한글 > 영어 > 기타 순으로 정렬하기

SQLite DB 테이블 조회할 때 한글과 영어가 섞인 컬럼에 대해서 정렬을 하게되면, SQLite는 기본적으로 기타문자(특수문자,숫자), 영어, 한글 순서로 정렬을 합니다. 보통 ORDER BY 절에 COLLATE LOCALIZED 키워드를 사용하는데, (사실 이게 뭔지는 저도 잘 모르지만) 사용자 로케일이 ko_KR이더라도 우리가 원하는대로 한글 - 영어 - 기타 순으로 정렬되지는 않더군요. 

ORDER BY name COLLATE LOCALIZED ASC; // 기타 - 영어 - 한글 순으로 정렬됨

1. 구분 컬럼을 이용한 방법

우리나라 사용자에게 좀더 자연스러운, 한글 -> 영어 -> 기타 순서로 정렬하기 위해 고민하다가 검색해보니 역시 유용한 팁이 있네요.

[한글/영어 Table을 Quary시에 sort하기]

해당 컬럼 값이 한글인지, 영어인지, 기타인지 구분해주는 구분컬럼을 두어서 1, 2, 3 식으로 할당을 합니다. 그리고 ORDER BY 절에 이 구분컬럼으로 먼저 정렬을 하면 조회목록에서 한글이 영어보다 먼저 나오게 됩니다. 위 링크에 나오는 것처럼 구분컬럼을 TAG라고 하면,

ORDER BY tag, name COLLATE LOCALIZED ASC; // TAG 컬럼으로 먼저 정렬하므로 한글 - 영어 - 기타 순으로 정렬됨

그러나 TAG와 같은 구분컬럼을 이용하는 경우에는 복잡성이 증가하고 유연성이 떨어집니다. 

우선 테이블 컬럼이 늘어나게 되고, NAME 컬럼에 값이 추가/수정될 때마다 이게 한글인지 영어인지 기타 인지 판단하여 TAG 컬럼 값 역시 적절히 추가/수정해줘야 합니다(이런 일에는 트리거가 유용하겠죠). 우리에게 테이블에 컬럼을 추가할 수 있는 권한이 없을 수도 있습니다. 만약 언어 로케일 설정 또는 화면별로 정렬 순서를 다르게 가져가야 한다면 TAG 컬럼을 활용하기 어려울 수도 있습니다.

2. ORDER BY 절에서 CASE 문을 이용하는 방법

이런 구분컬럼을 추가하지 않아도 우리가 원하는대로 정렬할 수 있습니다. ORDER BY 절에서 CASE 문을 사용해서 동적으로 한글/영어/기타로 구분하면 됩니다.

ORDER BY (CASE WHEN substr(name ,0, 1) BETWEEN 'ㄱ' AND '힣' THEN 1 

                           WHEN substr(name, 0, 1) BETWEEN 'A' AND 'Z' THEN 2

                           WHEN substr(name, 0, 1) BETWEEN 'a' AND 'z' THEN 2

                           ELSE 3 

                 END), 

                 name COLLATE LOCALIZED ASC

제가 SQL 전문가가 아니기 때문에 더 좋은 쿼리가 있는지는 모르겠습니다. 이 방법은 테이블을 고칠 필요가 없으며, 언어 로케일이나 화면 등 상황에 따라서 ORDER BY 절에서 정렬 순서를 수정하면 되므로 유연합니다. 동일한 CASE 구문이 여러 곳에서 쓰인다면 SQL 함수로 만들거나 자바 메소드로 만들어두면 됩니다. 다만 구분컬럼을 두는 1번 방법보다는 성능면에서 안 좋습니다. 그러나 테스트해 본 바로는 생각보다 그리 느려지지 않았습니다.

ps. 위 2가지 방법 모두 완벽한 정렬은 아닙니다. 첫글자 이후로는 기타 - 영어 - 한글 순으로 소팅됩니다. 즉 아래와 같이 정렬됩니다.

 - 가나다123

 - 가나다abc

 - 가나다라마바

ps2. SQLite가 COLLATE LOCALIZED 키워드를 어떻게 해석하는지 low level의 소스를 분석해보면 좀더 근본적인 해결 방법이 있을것 같습니다.