Oracle 데이터베이스의 substr() 함수详解 및 활용

출처: https://www.cnblogs.com/dshore123/p/7805050.html

1. substr 함수 문법 (문자열 추출 함수로 불림)

형식1: substr(문자열, 시작위치, 길이);

형식2: substr(문자열, 시작위치);

설명:

형식1:

문자열: 추출할 원본 문자열
시작위치: 추출을 시작할 위치 (주의: 0 또는 1인 경우 모두 첫 번째 문자부터 시작)
길이: 추출할 문자 수

형식2:

문자열: 추출할 원본 문자열
시작위치: 지정된 위치에서 뒤에 있는 모든 문자를 추출

2. 실습 예제

형식1:

<strong>1</strong><strong>、</strong>select substr('HelloWorld',0,3) value from dual; // 결과: Hel, H부터 3개 문자 추출
 <strong>2</strong><strong>、</strong>select substr('HelloWorld',1,3) value from dual; // 결과: Hel, H부터 3개 문자 추출
 3、select substr('HelloWorld',2,3) value from dual; // 결과: ell, e부터 3개 문자 추출
 4、select substr('HelloWorld',0,100) value from dual; // 결과: HelloWorld, 100은 문자열 길이보다 크지만 정상 반환
 <strong>5</strong>、select substr('HelloWorld',5,3) value from dual; // 결과: oWo
 <strong>6</strong><strong>、</strong>select substr('Hello World',5,3) value from dual; // 결과: o W (공백도 문자로 간주, 결과는 <strong>o 공백 W</strong>)
 <strong>7</strong>、select substr('HelloWorld',-1,3) value from dual; // 결과: d (뒤에서 첫 번째 문자부터 1개 추출, 3개가 아님. 아래 빨간색 주석 참조)
 <strong>8</strong>、select substr('HelloWorld',-2,3) value from dual; // 결과: ld (뒤에서 두 번째 문자부터 2개 추출, 3개가 아님. 아래 빨간색 주석 참조)
 <strong>9</strong>、select substr('HelloWorld',-3,3) value from dual; // 결과: rld (뒤에서 세 번째 문자부터 3개 추출)
<strong>10</strong>、select substr('HelloWorld',-4,3) value from dual; // 결과: orl (뒤에서 네 번째 문자부터 3개 추출)

(참고: 시작 위치가 0 또는 1일 경우 모두 첫 번째 문자부터 추출 (예: 1과 2)) (참고: 문자열 중간에 공백이 있으면 그 공백도 문자로 포함 (예: 5와 6)) (참고: 7~10번은 모두 3개 문자를 추출하려 하지만 결과가 3개가 아님. |a| ≤ b 일 때 a의 개수만큼 추출 (예: 7, 8, 9); |a| ≥ b 일 때는 b의 개수만큼 추출, 위치는 a에 의해 결정 (예: 9, 10))

형식2:

11、select substr('HelloWorld',0) value from dual;  // 결과: HelloWorld, 모든 문자 추출
12、select substr('HelloWorld',1) value from dual;  // 결과: HelloWorld, 모든 문자 추출
13、select substr('HelloWorld',2) value from dual;  // 결과: elloWorld, e부터 끝까지 추출
14、select substr('HelloWorld',3) value from dual;  // 결과: lloWorld, l부터 끝까지 추출
<strong>15</strong>、select substr('HelloWorld',-1) value from dual;  // 결과: d, 마지막 문자부터 1개 추출
<strong>16</strong>、select substr('HelloWorld',-2) value from dual;  // 결과: ld, 마지막 문자부터 2개 추출
<strong>17</strong>、select substr('HelloWorld',-3) value from dual;  // 결과: rld, 마지막 문자부터 3개 추출

(참고: 매개변수가 2개일 때, 음수이든 양수이든 마지막 문자부터 역방향으로 추출 (예: 15, 16, 17))

3. 실습 화면 예시:

예제1

예제2

예제5

예제6

예제7

예제8

예제9

예제10

예제15

예제16

예제17

4) 전체 함수 예제

 1 create or replace function generate_request_code return varchar2 AS
 2 
 3  -- 함수 목적: 요청 코드 자동 생성
 4  v_mca_no   mcode_apply.mca_no%TYPE; -- mcode_apply 테이블의 mca_no 필드 타입과 동일한 변수 생성
 5 
 6  CURSOR get_max_mca_no IS -- 커서 선언
 7      SELECT max(substr(mca_no, 11, 3)) -- 최대 요청 코드 추출, 마지막 3자리만 선택 (예: 001, 002...00n)
 8      FROM  mcode_apply 
 9      WHERE  substr(mca_no, 3, 8) = to_char(sysdate, 'YYYYMMDD'); -- 날짜 부분 추출 (예: 20170422), to_char(): 날짜를 문자열로 변환
10 
11  v_requestcode VARCHAR2(3); -- 결과 저장 변수
12 
13  BEGIN
14     OPEN get_max_mca_no; 
15     FETCH get_max_mca_no INTO v_requestcode; -- 커서 결과를 변수에 할당
16     CLOSE get_max_mca_no;
17 
18  IF v_requestcode IS NULL THEN         
19    v_requestcode := NVL(v_requestcode, 0);  -- NULL일 경우 0으로 대체
20  END IF;
21 
22    v_requestcode:= lpad(v_requestcode+1,3,'0'); -- 증가 후 왼쪽에 0 추가하여 3자리 숫자 생성, lpad(): 왼쪽 채우기 함수
23    v_mca_no:='MA'||to_char(sysdate,'YYYYMMDD')||v_requestcode; -- 최종 요청 코드 생성 (예: MA20170422001; MA20170422002; ... MA2017042200N)
24  
25  RETURN '0~,'||v_mca_no; 
26 
27 END ;

참고: 해당 함수를 테스트하려면 Oracle DB에 복사하여 함수명 "generate_request_code"를 우클릭 후 Test 실행. 테스트 시 사용하는 테이블명과 필드명은 실제 구조에 맞게 변경 필요

태그: Oracle substr 문자열 처리 SQL 함수 데이터베이스

8월 14일 05:46에 게시됨

괴물 클럽