[Oracle/SQL] 문자함수 - SUBSTR, INSTR, TRIM 등

<Oracle 함수>

Ⅰ. 단일행 함수

     1. 문자함수 

     2. 숫자함수

     3. 날짜함수

     4. 변환함수 : 숫자 → 문자, 날짜 → 문자

     5. 기타함수(일반함수) : NVL, CASE, DECODE  

Ⅱ. 다중행 함수

 

<문자함수>

 - 문자 저장 데이터형 : CHAR, VARCHAR2, CLOB 

 

1. 변환함수

 

1) UPPER 

 - 형식 : UPPER('문자열') 

 - 기능 : 문자열을 대문자로 변환시켜 준다. 

 - ex) UPPER('Hello, world!') ==> HELLO, WORLD 

 

2) LOWER

 - 형식 : LOWER('문자열') 

 - 기능 : 소문자로 변환시켜 준다. 

 - ex) LOWER('Hello, WORLD!') ==> hello world 

 

3) INITCAP 

 - 형식 : INITCAP('문자열') 

 - 기능 : 첫 글자만 대문자로 바꿔준다. 

 - ex) INITCAP('hello, world!') ==> Hello, World!

 - ex) INITCAP('hello this is 2nd feb 2020') ==> Hello This Is 2nd Feb 2020

 

-- UPPER/LOWER/INITCAP 예시) 
SELECT UPPER('Hello, world!') FROM DUAL;  --결과: HELLO, WORRLD!
SELECT LOWER('Hello, WORLD!') FROM DUAL;  --결과: hello, world!
SELECT INITCAP('hello, world!') FROM DUAL; --결과: Hello, world!
SELECT INITCAP('hello this is 2nd feb 2020') FROM DUAL;  --결과: Hello This Is 2nd Feb 2020

 

2. 제어함수 

 

1) SUBSTR

 - 형식 : SUBSTR('문자열', 시작위치, 길이) 

 - 기능 : 문자열을 자른다. (자르기 시작하는 위치와 잘랐을 때의 길이 지정)  

 - ex) SUBSTR('Hello, world!', 3, 6) ==> 'llo, w'

 - 함수명 유래 : 'subtract(빼다)' + String(문자)  

 - Java와 비교 : Java의 substring과 유사하다.

 - 마지막 arguement(길이)는 생략 가능하다. 생략 시, 시작위치부터 문자열 끝까지 가져온다.

   ex) SUBSTR('Hello, world!', 8) ==> 'world!'

 - 시작위치가 +이면 앞에서부터, -이면 뒤에서부터 자른다. 

   ex) SUBSTR('Hello, world!', 3) ==> 'llo, world!'

   ex) SUBSTR('Hello, world!', -3) ==> 'ld!'

 - 오라클은 숫자의 시작이 0이 아니라 1이라는 것에 주의해야 한다!

-- SUBSTR 예시) 
SELECT SUBSTR('Hello, world!', 3, 6) FROM DUAL;  --결과: llo, w
SELECT SUBSTR('Hello, world!', 8) FROM DUAL;  --결과: world!
SELECT SUBSTR('Hello, world!', 3) FROM DUAL;  --결과: llo, world!
SELECT  SUBSTR('Hello, world!', -3) FROM DUAL;  --결과: ld!

 

2) INSTR

 - 형식 : INSTR('문자열', '비교하고자 하는 값', 시작할 위치, 몇 번째 비교값인지) 

 - 기능 : 내가 찾고자 하는 문자열이 전체 문자열에서 몇번째 위치에 있는지 위치를 알려준다.

 - INSTR의 결과값은 위치번호이다. 

 - ex) INSTR('Hello, world!', 'e', 1, 1) ==> 2

 - 함수명 유래 : 문자열에서 몇 번째 위치인지 (In string)에서 INSTR이 된 것 같다. 

 - Java와 비교 : Java의 index, last index of 와 유사하다.

 - 마지막 argument(몇 번째 비교값인지)는 생략 가능하다. 생략 시, 비교값 중 첫번째 것을 이용한다. (디폴트:1) 

   ex) INSTR('Hello, world!', 'l', 1) ==> 3

 - 마지막 argument는 문자열 내에 비교하고자 하는 값이 여러개가 있을 때, 그 중 몇번째 비교값을 말하는건지 알려준다.

   ex) INSTR('Hello, world!', 'l', 1, 3) ==> 11 (3번째 l의 위치를 묻는 것.) 

 - 시작위치가 1이 아닐 경우, 시작위치 전의 문자는 비교를 하지 않는다. 번호는 시작위치가 1일 때와 동일하다. 

   ex) INSTR('Hello, world!', 'o', 1, 1) ==> 5

   ex) INSTR('Hello, world!', 'o', 5, 1) ==> 5

   ex) INSTR('Hello, world!', 'o', 7, 2) ==> 0 (7번째부터 검색하기 때문에 2번째 o가 없다고 생각함.) 

   ex) INSTR('Hello, world!', 'o', 7, 1) ==> 9 

-- INSTR 예시) 
SELECT INSTR('Hello, world!', 'e', 1, 1) FROM DUAL;  --결과: 2
SELECT INSTR('Hello, world!', 'l', 1) FROM DUAL;  --결과: 3
SELECT INSTR('Hello, world!', 'l', 1, 3) FROM DUAL;  --결과: 11
SELECT INSTR('Hello, world!', 'o', 1, 1) FROM DUAL;  --결과: 5
SELECT INSTR('Hello, world!', 'o', 5, 1) FROM DUAL;  --결과: 5
SELECT INSTR('Hello, world!', 'o', 7, 2) FROM DUAL;  --결과: 0
SELECT INSTR('Hello, world!', 'o', 7, 1) FROM DUAL;  --결과: 9

 

3) CONCAT

 - 형식 : CONCAT('문자열1', '문자열2')

 - 기능 : 문자열1과 문자열2를 합쳐준다. (문자결합) 

 - ex) CONCAT('안녕', '하세요') ==> '안녕하세요'

 - 함수명 유래 : concatenate(연결하다) 

 - 보통 CONCAT 보다는 ||을 많이 사용한다. 

   ex) '안녕'||'하세요' ==> '안녕하세요'

 

4) REPLACE

 - 형식 : REPLACE('문자열', '찾을문자', '변환문자') 

 - 기능 : 문자열에서 특정 문자를 다른 문자로 바꾼다.

 - ex) REPLACE('Hello, world!', 'o', 'a') ==> 'Hella, warld!'

 

 

3. 추가함수

 

1) LPAD

 - 형식 : LPAD('문자열', 총 문자길이, '채움문자') 

 - 기능 : 지정한 길이만큼 왼쪽부터 특정 문자로 채워준다.

 - ex) LPAD('admin' , 10 , '*') ==> '*****admin'

 - 함수명 유래 : Left-padded(왼쪽에 채워줬다)로 추정. 

 - 원래 문자열의 길이가 지정한 총 문자길이보다 클 경우, 원래 문자열을 지정한 길이만큼이 되도록 자른다. 

 - ex) LPAD('admin12345', 5, '*') ==> 'admin'

 

2) RPAD 

 - 형식 : RPAD('문자열', 총 문자길이, '채움문자') 

 - 기능 : 지정한 길이만큼 오른쪽부터 특정 문자로 채워준다.

 - ex) RPAD('admin' , 10 , '*') ==> 'admin*****'

 - 함수명 유래 : Right-padded(오른쪽에 채워줬다)로 추정. 

 - 원래 문자열의 길이가 지정한 총 문자길이보다 클 경우, 원래 문자열을 지정한 길이만큼이 되도록 자른다. 

 - ex) RPAD('admin12345', 5, '*') ==> 'admin' 

 

 

4. 제거함수

 

1) LTRIM

 - 형식 : LTRIM('문자열', '반복되는 문자') 

 - 기능 : 문자열의 왼쪽 공백을 제거하거나, 문자열 왼쪽의 반복적인 문자를 제거한다. 

 - ex) LTRIM('AAABBB', 'A') ==> 'BBB' (왼쪽에 반복되는 문자 A 제거) 

 - 두번째 argument(반복되는 문자)는 생략 가능하다. 생략 시, 문자가 아니라 공백을 제거한다. 

   ex) LTRIM(' AAABBB') ==> AAABBB (왼쪽 공백 제거) 

 - ex) LTRIM('ABABCCCBB', 'AB') ==> 'CCCBB' (왼쪽에 반복되는 문자 AB 제거) 

 - ex) LTRIM('AA ABCDEF', 'A') ==> ' ABCDEF'

        (왼쪽에 반복되는 문자 A 제거. 중간에 공백은 A와 다른 하나의 문자이므로 제거하지X) 

 - 반복되는 문자 여러개를 입력한 경우, 왼쪽에 개별 문자가 있는지 하나씩 체크해서 없앤다. 

   즉, 아래와 같은 경우에는 왼쪽에 반복되는 'A', 'B', 'C' 각각이 있는지를 체크하고 제거한다. 

   그러므로 결과가 'AB DCCCBB'(ABC를 하나의 셋트로 보고 완벽하게 ABC가 붙어있는 것만 제거) 가 아니라 ' DCCCBB'가 되는 것이다. (왼쪽에서부터 'A', 'B', 'C'가 있는지 각각을 검사해서 제거) 

   ex) LTRIM('ABCAB DCCCBB', 'ABC') ==> ' DCCCBB' 

 

2) RTRIM

 - 형식 : RTRIM('문자열', '반복되는 문자') 

 - 기능 : 문자열의 오른쪽 공백을 제거하거나, 문자열 오른쪽의 반복적인 문자를 제거한다.

 - ex) RTRIM('AAABBB', 'B') ==> 'AAA' (오른쪽에 반복되는 문자 B 제거) 

 - 두번째 argument(반복되는 문자)는 생략 가능하다. 생략 시, 문자가 아니라 공백을 제거한다. 

 - ex) RTRIM('AAABBB  ') ==> 'AAABBB' (오른쪽 공백 제거) 

 - 반복되는 문자 여러개를 입력한 경우, 오른쪽에 개별 문자가 있는지 하나씩 체크해서 없앤다. 

   즉, 아래와 같은 경우에는 오른쪽에 반복되는 'A', 'B' 각각이 있는지를 체크하고 제거한다.

   그러므로 결과가 'CCCBB'가 아니라 'CCC'가 되는 것이다. 

   ex) RTRIM('CCCBBABAB', 'AB') ==> 'CCC'

   ex) RTRIM('ABC12345543', '345') ==> 'ABC12'

   (https://www.oracletutorial.com/oracle-string-functions/oracle-rtrim/ 참고) 

 

3) TRIM 

 - 형식 : TRIM('문자열')   

            TRIM('제거할 문자' FROM '문자열') 

 - 기능 : 문자열의 양쪽 공백을 제거하거나, 문자열 양쪽에서 지정된 문자를 제거한다.

 - ex) TRIM('   Hello, world!   ') ==> 'Hello, world!'

 - ex) TRIM('TRIM('A' FROM 'AAABABABAAA') ==> 'BABAB'

 - ex) TRIM('A' FROM 'AACDEFA') ==> 'CDEF'

 - TRIM(옵션, '제거할 문자' FROM '문자열') 의 형태로도 사용 가능하다.

   옵션을 쓰지 않으면 both로 인식한다. 

 - 옵션 : leading, trailing, both 

   (1) leading : 문자열 앞 반복 문자 제거

        ex) TRIM(LEADING 'A' FROM 'AAACDEAAA') ==> 'CDEAAA'

   (2) trailing : 문자열 뒤 반복 문자 제거

        ex) TRIM(TRAILING 'A' FROM 'AAACDEAAA') ==> 'AAACDE'

   (3) both : 문자열 앞/뒤 반복 문자 제거 

        ex) TRIM(both 'A' FROM 'AAACDEAAA') ==> 'CDE'

 

 

↓이 글이 도움이 됐다면 아래 ♡버튼을 눌러주세요! ↓