[Mysql] 컬럼값이 "학년/반/번호" 일 때 "/"로 나눈 값을 기준으로 정렬하기

MySQL에서 특정 패턴을 가진 문자열 데이터를 정렬하는 것은 데이터 관리 및 표시에 있어서 중요한 과제 중 하나입니다. 특히 "학년/반/번호"와 같이 하나의 컬럼에 여러 정보가 '/' 구분자를 통해 저장되어 있는 경우, 이를 개별적으로 나누어 정렬하는 것은 더욱 복잡해질 수 있습니다. 이번 글에서는 MySQL의 SUBSTRING_INDEX 함수와 CAST 함수를 활용하여 이러한 문자열을 각각의 구성 요소로 분할하고, 이를 기준으로 데이터를 정렬하는 방법을 알아보겠습니다.

 

 

1. 문제의 발단

mysql에서 테이블에 학년반번호를 저장하는데 여러 개 하기 귀찮아서 "학년/반/번호" 형식으로 하나의 컬럼에 저장해 두었습니다. 그런데  나중에 출력을 하려고 보니 순서가 뒤죽박죽 되어버렸네요.

name	gradenum
A	1/1/3
B	1/2/4
C	1/1/1
D	1/3/1

테이블은 위와 같이 되어있습니다. 

여기서 학년, 반, 번호 순으로 정렬을 하고 싶은데 어떻게 해야 할까요?

 

2. SUBSTRING_INDEX 함수의 활용

SUBSTRING_INDEX 함수는 문자열을 특정 구분자로 분할하여 원하는 부분을 추출하는 데 사용합니다.

세 가지 주요 매개변수를 가지는데 "문자열, 구분자, 위치 수"로 구분할 수 있습니다.

기본 문법은 SUBSTRING_INDEX(문자열, 구분자, 위치 수) 처럼 사용하면 됩니다.

예시: SUBSTRING_INDEX('학년/반/번호', '/', 1)은 '학년' 이라는 값을 얻을 수 있습니다.

 

3. 복합적인 문자열 분할 및 정렬

"학년/반/번호" 형태의 데이터를 학년, 반, 번호 순으로 정렬하기 위해 SUBSTRING_INDEX를 여러 번 사용하여 각각을 분리합니다.

예를 들어, 두 번째 '/' 이전의 문자열을 추출하고 싶다면 SUBSTRING_INDEX(SUBSTRING_INDEX('학년/반/번호', '/', 2), '/', -1)과 같이 사용하여 '반'를 얻을 수 있습니다.

이러한 방식으로 각 항목(학년, 반, 번호)을 분리한 후, 이를 기준으로 정렬할 수 있습니다.

그 후, CAST 함수를 사용하여 각 부분을 정수형으로 변환합니다.

이는 문자열이 아닌 숫자로 정렬하고자 할 때 필요한 과정입니다.

 

4. 쿼리 예시

다음 쿼리는 "학년/반/번호"를 각각 분할하여 숫자 기준으로 정렬하는 예시입니다:

SELECT gradenum 
FROM table_name 
ORDER BY CAST(SUBSTRING_INDEX(gradenum, '/', 1) AS UNSIGNED),
         CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(gradenum, '/', 2), '/', -1) AS UNSIGNED),
         CAST(SUBSTRING_INDEX(gradenum, '/', -1) AS UNSIGNED);

이 쿼리는 먼저 학년을 기준으로 정렬하고, 그 다음 반, 마지막으로 번호 순으로 정렬합니다.

name	gradenum
C	1/1/1
A	1/1/3
B	1/2/4
D	1/3/1

그러면 위와 같은 결과를 얻을 수 있습니다.

 

결론

이러한 방법을 통해 MySQL에서 "학년/반/번호"와 같은 형식의 데이터를 효과적으로 정렬할 수 있습니다. SUBSTRING_INDEX와 CAST 함수의 조합은 복잡해 보일 수 있지만, 이를 통해 데이터를 보다 체계적이고 명확하게 정렬하고 관리할 수 있게 됩니다. 이를 통해 보다 정교한 데이터 처리가 가능해질 것입니다.

 

함께하면 좋은글

 

[MYSQL] SUBSTRING_INDEX() 함수로 구분자 기준(콤마 분리) 분할하기

[MYSQL] 숫자, 문자열, 날짜시간 변환하기 CAST함수