오라클 DUAL, SUBSTR, INSTR, LPAD, REPLACE

환경
윈도우10 64비트
오라클 18c 익스프레스

 

DUAL

은 가상 테이블이다. 아래와 같이 테스트 용으로 사용

SELECT 'CRIS'
FROM DUAL;

---

행 줄이기 SUBSTR(), INSTR()

 

문자열 자르기 함수

SUBSTR( , , )

첫번째 인자는 문자열, 두번째 인자는 시작 번지, 세번째 인자는 개수

 

오라클은 인덱스 1부터 시작한다. 0을 넣어도 작동은 된다.

SELECT SUBSTR('CRIS', 0, 1) 
FROM DUAL;

SELECT SUBSTR('CRIS', 1, 1) 
FROM DUAL;

 

SUBSTR()함수로 데이터 잘라서 찾아낼 수도 있음.

SELECT *

FROM 테이블 

WHERE SUBSTR(열이름, 문자열 시작위치, 문자 개수) = '문자 또는 문자열';

 

예시 1

SELECT * 
FROM emp 
WHERE SUBSTR(ename, 1, 1) = 'M';

 

 

예시 2

SELECT * 
FROM student 
WHERE SUBSTR(jumin,1,2) = 76;

 

예시 3

-- student에서 부산에 사는 사람을 찾으시오. 
SELECT * 
FROM student 
WHERE SUBSTR(tel, 1, 3) = '051';

 

예시 4

 

-- student 2월에 태어난 사람을 찾으시오.
SELECT * 
FROM student 
WHERE SUBSTR(birthday, 4, 2) = '02';

 

INSTR( , , , )

 

SELECT instr(문자열,찾을 문자 또는 문자열, 시작 문자열 위치, n번째 검색결과)

FROM dual;

 

n1번 위치부터 찾아봐서 n2번째 찾을 문자또는 문자열의 위치

 

SELECT instr('02-5555-8989','-', 1, 2) 
FROM dual;

 

1번 위치부터 찾아봐서 2번째로 찾아지는 -의 위치

 

 

 

 

중간번호 찾아내기

SUBSTR(), INSTR()

 

SELECT substr(찾을 데이터, 시작위치, 개수) AS 열의 별칭

from 테이블;

SELECT substr(tel, instr(tel, ')') + 1, instr(tel, '-') - instr(tel, ')') - 1) AS 중간번호, tel
from student;

지역번호 찾아내기

SELECT name, tel, SUBSTR(tel, 1, INSTR(tel,')') - 1) AS "AREA CODE"
FROM student WHERE deptno1 = 201;

 

 

REAPLCE( , , )

 

SELECT REPLACE(문자열, 바꾸고 싶은 문자 또는 문자열, 넣을 문자)

FROM 테이블

WHERE 열이름= 데이터;

SELECT REPLACE('ABC', 'A', 'F')
from dual;

예시 1

REPLACE( , , ), SUBSTR()

SELECT ename, REPLACE(ename, SUBSTR(ename, 2, 2), '--')
FROM emp
WHERE deptno = 20;

 

 

예시 2

REPLACE( , , ), SUBSTR()

ELECT name, jumin, REPLACE(jumin, SUBSTR(jumin, 7, 7), '-/-/-/-') "REPLACE" 
FROM student 
WHERE deptno1 = 101;

 

 

예시 3

REPLACE( , , ), SUBSTR()

SELECT name, tel, REPLACE(tel, SUBSTR(tel, 5, 3), '***') "REPLACE"
FROM student
WHERE deptno1 = 102;

예시 4

REPLACE( , , ), SUBSTR()

SELECT REPLACE(jumin, SUBSTR(jumin, 8, 6), '******')
FROM student;

---

지역번호만 *로 바꾸기

LPAD()

LPAD 함수는 지정한 길이 만큼 왼쪽부터 특정문자로 채워준다.

- 함수  :  LPAD("값", "총 문자길이", "채움문자")

SELECT LPAD(SUBSTR(tel, instr(tel,')')), instr(tel,'-') + 4, '*')
FROM student;

REPLACE(), SUBSTR(), INSTR()

SELECT REPLACE(tel, SUBSTR(tel, 1, instr(tel,')')-1), SUBSTR('***', 1, INSTR(TEL, ')') - 1))
FROM student;