오라클 특정위치 문자 치환 - olakeul teugjeong-wichi munja chihwan

REGEXP_REPLACE 함수는 주어진 문자열을 대상으로 정규식 패턴을 조사하여, 다른 문자로 대체하는 함수로, REGEXP_LIKE와 비슷하게 기존에 REPLACE를 확장한 개념이다.

//REGEXP_LIKE를 모르시는 분은 이글 참고해주세요.

https://sinsucoding.tistory.com/20

Oracle 정규식 추출 REGEXP_LIKE 함수(문자열포함 검색)

REGEXP_LIKE함수는 간단하게 설명드리자면 저희는 특정 문자열 포함을 검색할 때 LIKE함수를 사용하는데 시작 문자가 010,011,016인 문자열을 출력 할때 저희는 불편하게 or을 사용하여 나타내야 합니

sinsucoding.tistory.com

REGEXP_REPLACE 함수

-문 법: REGEXP_REPLACE(칼럼, 조건, 대체 값, position, occurrence)//position, occurrence값은 default값이 있다.

-position : Oracle이 문자열에서 특정 문자를 어디에서 찾아야 하는지 위치를 나타냄(시작 위치) default값이 1로 설정                  되어 있으므로, 문자열의 처음부터 검색을 시작.

-occurrence: 검색하고자 하는 문자열에서 특정 문자의 발생 횟수 default값이 1로 설정되어 있으며, 이는 문자열에서

                 첫번째 발생 pattern을 찾는다는 의미.

ex1) 모든 숫자를 특수 기호로 변경하기

ex2) 공백이 두칸이상인곳 공백 제거하기

ex3) 특정 문자열의 형태를 다른 형태로 바꾸기

INSTR

• INSTR(문자열, 검색할 문자, 시작지점, n번째 검색단어) 함수는 찾는 문자의 위치를 반환한다.
• 찾는 문자가 없으면 0을 반환한다.
• 찾는 단어 앞글자의 인덱스를 반환한다.
• 기본으로 왼쪽부터 시작하여 우측방향으로 스캔한다.
• 시작지점에 음수를 쓸 경우 우측에서 시작하기 때문에 스캔반향이 좌측방향이다.

사용 방법에 대해 알아보자.

-- 1) 찾는 단어가 없기에 0을 반환
SELECT INSTR('CORPORATE FLOOR','OK') AS idx FROM DUAL; -- 0
 
-- 단어의 앞글자 인덱스를 반환
 
-- 2) PO 단어를 찾아라
SELECT INSTR('CORPORATE FLOOR','PO') AS idx FROM DUAL; -- 4 
 
-- 3) 6번째 부터 시작해서 해당 단어 OR 위치를 찾아라
SELECT INSTR('CORPORATE FLOOR','OR',6) AS idx FROM DUAL; -- 14
 
-- 4) 2번째 부터 시작해서 나오는 값중에 2번째 값의 위치를 찾아라
SELECT INSTR('CORPORATE FLOOR','OR',2,2) AS idx FROM DUAL; -- 5
 
-- 5) 우측에서 3번째 위치부터 시작해서 왼쪽방향으로 검색하고 2번째 나오는 값의 위치를 찾아라
SELECT INSTR('CORPORATE FLOOR','OR',-3,2) AS idx FROM DUAL; -- 2

위 사용방법에 대해 하나하나 설명하자면 다음과 같다. (번호 순서대로)

1번은 주석 그대로 찾는 단어가 없으면 0을 반환한다.

2번은 시작지점과 몇번째 검색단어를 찾을지는 생략이 가능하나 그럴경우 처음부터 스캔해서 첫번째 값의 위치를 반환한다.

3번은 시작지점을 입력할경우 그 위치부터 우측으로 스캔해서 나오는 값의 위치를 반환한다.

4번은 2번째부터 시작해서 2번째 나오는 값은 (C)ORPOR 이므로 두번째 OR의 인덱스를 반환한다.

5번은 음수를 사용했기 때문에 우측기준 3번째 위치부터 시작해 좌측방향으로 스캔해서 나오는 두번째 OR 값의 인덱스인 2를 반환하는 것이다.

<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'

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