데이터베이스 작업을 하다 보면 '19/5'처럼 슬래시(/)로 구분된 문자열에서 슬래시 뒤의 숫자 부분을 일정한 자릿수로 맞추고, 남는 자리를 0으로 채워야 하는 경우가 있습니다. 예를 들어 '19/5'를 '19/0005'처럼 표현하는 것이죠.
MySQL에서는 LEFT(), SUBSTRING(), LPAD(), CONCAT() 함수를 조합하면 이러한 형식화를 손쉽게 처리할 수 있습니다. 실제 예제를 통해 단계별로 살펴보겠습니다.
1. 테이블 생성하기
먼저 데모용 테이블을 생성합니다.
mysql> create table DemoTable1369
-> (
-> BatchId varchar(20)
-> );
Query OK, 0 rows affected (0.46 sec)
2. 샘플 데이터 삽입하기
INSERT 명령어를 사용해 슬래시로 구분된 숫자 형식의 레코드를 여러 개 삽입합니다.
mysql> insert into DemoTable1369 values('19/5');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1369 values('19/78');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1369 values('19/567');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1369 values('19/1234');
Query OK, 1 row affected (0.11 sec)3. 저장된 데이터 확인하기
SELECT 문으로 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1369;
실행 결과는 다음과 같습니다.
+---------+
| BatchId |
+---------+
| 19/5 |
| 19/78 |
| 19/567 |
| 19/1234 |
+---------+
4 rows in set
4. LPAD를 활용한 0 채우기 쿼리
이제 핵심인 숫자-문자열 형식화 쿼리입니다. 슬래시 앞부분은 그대로 유지하고, 슬래시 뒤의 숫자 부분만 추출하여 지정한 자릿수만큼 왼쪽에 0을 채웁니다.
전체 필드 너비는 가장 긴 값인 '1234'의 자릿수인 4자리로 설정했습니다.
mysql> select
-> concat(left(BatchId,3), lpad(substring(BatchId, 4), 4, '0'))
-> from DemoTable1369;
쿼리 동작 원리
- left(BatchId, 3): 문자열의 앞 3글자('19/')를 그대로 가져옵니다.
- substring(BatchId, 4): 4번째 위치부터 끝까지, 즉 슬래시 뒤의 숫자 부분만 추출합니다.
- lpad(..., 4, '0'): 추출한 숫자가 4자리가 되도록 왼쪽에 '0'을 채웁니다.
- concat(): 앞부분과 0으로 채워진 숫자를 하나의 문자열로 합칩니다.
실행 결과는 다음과 같습니다.
+--------------------------------------------------------------+
| concat(left(BatchId,3), lpad(substring(BatchId, 4), 4, '0')) |
+--------------------------------------------------------------+
| 19/0005 |
| 19/0078 |
| 19/0567 |
| 19/1234 |
+--------------------------------------------------------------+
4 rows in set (0.00 sec)
마무리
이처럼 LPAD() 함수와 문자열 분리 함수들을 조합하면 슬래시, 하이픈 등 어떤 구분자 뒤의 숫자든 원하는 자릿수에 맞춰 0으로 채울 수 있습니다. 배치 번호, 주문 코드, 상품 코드처럼 고정된 형식의 ID를 관리할 때 매우 유용하게 활용할 수 있습니다. 참고로 오른쪽을 채우고 싶다면 RPAD() 함수를 사용하면 됩니다.