[Oracle] VARCHAR2 정렬 방식과 자연 정렬 구현 (ORDER BY)

📌 VARCHAR2 정렬은 왜 우리가 원하는 순서대로 안 나올까?

VARCHAR2는 문자형 데이터이다.
ORDER BY로 정렬할 때는 문자열 기준, 즉 아스키코드 순서로 정렬된다.

아스키 정렬 순서

• 숫자 (0–9) → 대문자 (A–Z) → 소문자 (a–z)
• 특수문자는 이보다 앞쪽 또는 중간에 위치
• 그래서 "varchar_10"이 "varchar_2"보다 앞에 나올 수 있음

---

VARCHAR2 + 숫자 조합의 정렬 기준

패턴 1. varchar_1, varchar_2, ..., varchar_10
→ varchar_ 부분이 동일하다면, 뒤의 숫자도 문자처럼 정렬됨 (즉, "10"이 "2"보다 먼저)

패턴 2. data1, abc2, test10 등 접두어가 다르면
→ 왼쪽 문자부터 하나씩 아스키코드 기준으로 비교 정렬

---

❓ 우리가 원하는 "자연 정렬"이란?

varchar_1, varchar_2, ..., varchar_10
→ 실제로는 "1"부터 "10"까지 숫자 순으로 정렬되길 원함

---

✅ 방법 1: _ 이후 숫자만 추출해서 정렬

SELECT col_char, col_varchar2, col_number
FROM datatype_example
ORDER BY TO_NUMBER(SUBSTR(col_varchar2, INSTR(col_varchar2, '_') + 1));

• INSTR(colvarchar2, '') : '_' 위치 찾기
• SUBSTR(... + 1) : 숫자 부분만 추출
• TO_NUMBER(...) : 문자열을 숫자로 변환

✅ 방법 2: 앞 패턴이 일정하지 않거나 구분자가 없을 경우

• Oracle SQL에서는 정규표현식 함수 REGEXP_SUBSTR를 이용해 숫자만 추출할 수 있다.

SELECT col_char, col_varchar2, col_number 
	FROM datatype_example 
	ORDER BY TO_NUMBER(REGEXP_SUBSTR(col_varchar2, '[0-9]+'));

REGEXP_SUBSTR 란?

문자열에서 원하는 패턴을 추출할 수 있게 해주는 함수이다.

1. '[0-9]': 0부터 9 사이의 숫자 한 자리
2. '[0-9]+': 숫자가 1개 이상 연속되는 경우 → 예: 123, 45, 7

즉, 이 정규표현식은 처음 나타나는 숫자 연속 부분을 찾아준다.

복합 숫자 문자열도 정렬 가능

SELECT *
FROM ex_table
ORDER BY TO_NUMBER(REGEXP_SUBSTR('qw12ww34', '[0-9]+', 1, 1));

• qw12ww34 → 12 기준으로 정렬
• 첫 번째 숫자 기준으로 정렬
  
  

숫자만 남기고 싶다면?

SELECT REGEXP_REPLACE(col_varchar2, '[^0-9]', '') AS only_digits
FROM your_table;

• qw12ww34 → 1234
• 문자 제거하고 숫자만 추출 가능
  
  

✅ 숫자형태의 문자칼럼 정렬방법

varchar자리에 문자형을 사용하지 않고, 모두 숫자만 입력하였을 때는
order by 뒤에 to_number(칼럼명)을 사용하면 숫자 크기대로 정렬 가능하다.

SELECT col_varchar 
 FROM datatype_example 
 ORDER BY to_number(col_varchar);