📚 Study/Oracle
Oracle :: 문자열 처리 함수 (SUBSTR, INSTR, LENGTH, REVERSE, RPAD, LPAD, RTRIM, LTRIM, TRANSLATE, REPLACE)
bono-hye
2023. 10. 22. 20:38
■ SUBSTR(①, ②, ③)
-문자열을 추출하는 기능을 가진 함수
- 첫 번째 파라미터 : 대상 문자열 (추출의 대상, TARGET)
- 두 번째 파라미터 : 추출을 시작하는 위치 (인덱스, START) → 자바와는 다르게 인덱스는 1부터 시작
- 세 번째 파라미터 : 추출할 문자열의 갯수 (갯수, COUNT) → 생략할 경우, 문자열의 길이 끝까지 추출
--○ SUBSTR() 갯수 기반 / SUBSTRB() 바이트 기반
SELECT ENAME "COL1"
,SUBSTR(ENAME, 1, 2) "COL2"
FROM EMP;
--> 문자열을 추출하는 기능을 가진 함수
-- 첫 번째 파라미터 값은 대상 문자열(추출의 대상, TARGET)
-- 두 번째 파라미터 값은 추출을 시작하는 위치(인덱스, START) → 인덱스는 1 부터 시작...
-- 세 번째 파라미터 값은 추출할 문자열의 갯수(갯수, COUNT) → 생략 시, 문자열의 길이 끝까지
--==>>
/*
SMITH SM
ALLEN AL
WARD WA
JONES JO
MARTIN MA
BLAKE BL
CLARK CL
SCOTT SC
KING KI
TURNER TU
ADAMS AD
JAMES JA
FORD FO
MILLER MI
*/
SELECT ENAME "COL1"
, SUBSTR(ENAME, 3, 2) "COL2"
, SUBSTR(ENAME, 3, 5) "COL3"
, SUBSTR(ENAME, 3) "COL4"
, SUBSTR(ENAME, 6, 1) "COL5"
FROM EMP;
--==>>
/*
SMITH IT ITH ITH
ALLEN LE LEN LEN
WARD RD RD RD
JONES NE NES NES
MARTIN RT RTIN RTIN N
BLAKE AK AKE AKE
CLARK AR ARK ARK
SCOTT OT OTT OTT
KING NG NG NG
TURNER RN RNER RNER R
ADAMS AM AMS AMS
JAMES ME MES MES
FORD RD RD RD
MILLER LL LLER LLER R
*/
--○ TBL_SAWON 테이블에서 성별이 남성인 사원만
-- 사원번호, 사원명, 주민번호, 급여 항목을 조회한다.
-- 단, SUBSTR() 함수를 활용하여 처리할 수 있도록 한다.
SELECT SANO, SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SUBSTR(JUBUN, 7, 1) = '1'
OR SUBSTR(JUBUN, 7, 1) = '3'; -- SUBSTR 추출한 것은 문자타입! 그냥 1, 3으로 쓰는건 오라클의 형변환에 의지한 것임. 엄격하게 따지면 틀린 것!
--==>>
/*
1001 강혜성 9710171234567 3000
1008 선동열 6909101234567 2000
1009 이이경 0505053234567 1500
1011 이윤수 9501061234567 4000
1013 남진 6511111234567 2000
1014 이주형 9904171234567 2000
1015 남궁민 0202023234567 2300
*/
SELECT SANO"사원번호", SANAME"사원명", JUBUN"주민번호", SAL"급여"
FROM TBL_SAWON
WHERE SUBSTR(JUBUN, 7, 1) IN ('1', '3');
■ INSTR(①, ②, ③, ④)
- 첫 번째 파라미터 : 대상 문자열, TARGET
- 두 번째 파라미터 : TARGET이 등장하는 위치를 찾음
- 세 번째 파라미터 : 찾기 시작하는 (스캔을 시작하는) 위치
- 네 번째 파라미터 : 몇 번째 등장하는 값을 찾을 것인지에 대한 설정 → 1일 경우 생략 가능
--○ INSTR()
SELECT 'ORACLE ORAHOME BIORA' "COL1"
, INSTR('ORACLE ORAHOME BIORA', 'ORA', 1, 1) "COL2" -- 1
, INSTR('ORACLE ORAHOME BIORA', 'ORA', 1, 2) "COL3" -- 8
, INSTR('ORACLE ORAHOME BIORA', 'ORA', 2, 1) "COL4" -- 8
, INSTR('ORACLE ORAHOME BIORA', 'ORA', 2) "COL5" -- 8
, INSTR('ORACLE ORAHOME BIORA', 'ORA', 2, 3) "COL6" -- 0
FROM DUAL;
--==>> ORACLE ORAHOME BIORA 1 8 8 8 0
--> 첫 번째 파라미터 값에 해당하는 문자열에서 (대상 문자열, TARGET)
-- 두 번째 파라미터 값을 통해 넘겨준 문자열이 등장하는 위치를 찾아라!
-- 세 번째 파라미터 값은 찾기 시작하는(스캔을 시작하는) 위치
-- 네 번째 파라미터 값은 몇 번째 등장하는 값을 찾을 것인지에 대한 설정 → (1일 경우 생략 가능. CO4, COL5 같은 구문임)\
SELECT '나의오라클 집으로오라 합니다.' "COL1"
, INSTR('나의오라클 집으로오라 합니다.', '오라', 1) "COL2" -- 3
, INSTR('나의오라클 집으로오라 합니다.', '오라', 2) "COL3" -- 3
, INSTR('나의오라클 집으로오라 합니다.', '오라', 10) "COL4" -- 10
, INSTR('나의오라클 집으로오라 합니다.', '오라', 11) "COL5" -- 0
FROM DUAL;
--==>> 나의오라클 집으로오라 합니다. 3 3 10 0
--> 마지막 파라미터 값을 생략한 형태로 사용 → 마지막 파라미터 → 1
■ LENGTH
- 문자열의 글자 수 추출
--○ LENGTH() 글자 수 / LENGTHB() 바이트 수
SELECT ENAME "COL1"
, LENGTH(ENAME) "COL2"
, LENGTHB(ENAME) "COL3"
FROM EMP;
--==>>
/*
SMITH 5 5
ALLEN 5 5
WARD 4 4
JONES 5 5
MARTIN 6 6
BLAKE 5 5
CLARK 5 5
SCOTT 5 5
KING 4 4
TURNER 6 6
ADAMS 5 5
JAMES 5 5
FORD 4 4
MILLER 6 6
*/
■ REVERSE
- 대상 문자열을 거꾸로 반환 (단, 한글은 제외)
--○ REVERSE()
SELECT 'ORACLE' "COL1"
, REVERSE('ORACLE') "COL2"
, REVERSE('오라클') "COL3"
FROM DUAL;
--==>> ORACLE ELCARO ???
-- 대상 문자열을 거꾸로 반환한다. (단, 한글은 제외)
■ REVERSE 실습
--○ 실습 테이블 생성(TBL_FILES)
CREATE TABLE TBL_FILES
( FILENE NUMBER(3)
, FILENAME VARCHAR2(100)
);
--==>> Table TBL_FILES이(가) 생성되었습니다.
--○ 데이터 입력(TBL_FILES)
INSERT INTO TBL_FILES VALUES(1, 'C:\AAA\BBB\CCC\SALES.DOC');
INSERT INTO TBL_FILES VALUES(2, 'C:\AAA\PANMAE.XXLS');
INSERT INTO TBL_FILES VALUES(3, 'D:\RESEARCH.PPT');
INSERT INTO TBL_FILES VALUES(4, 'C:\DOCUMENTS\STUDY.HWP');
INSERT INTO TBL_FILES VALUES(5, 'C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT');
INSERT INTO TBL_FILES VALUES(6, 'C:\SHARE\F\TEST\FLOWER.PNG');
INSERT INTO TBL_FILES VALUES(7, 'E:\STUDY\ORACLE\20231020_01_SCOTT.SQL');
--==>> 1 행 이(가) 삽입되었습니다. * 7
SELECT *
FROM TBL_FILES;
--==>>
/*
1 C:\AAA\BBB\CCC\SALES.DOC
2 C:\AAA\PANMAE.XXLS
3 D:\RESEARCH.PPT
4 C:\DOCUMENTS\STUDY.HWP
5 C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT
6 C:\SHARE\F\TEST\FLOWER.PNG
7 E:\STUDY\ORACLE\20231020_01_SCOTT.SQL
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
SELECT FILENE "파일번호"
, FILENAME "파일명"
FROM TBL_FILES;
--==>>
/*
--------- ----------------------------------------
파일번호 파일명
--------- ----------------------------------------
1 C:\AAA\BBB\CCC\SALES.DOC
2 C:\AAA\PANMAE.XXLS
3 D:\RESEARCH.PPT
4 C:\DOCUMENTS\STUDY.HWP
5 C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT
6 C:\SHARE\F\TEST\FLOWER.PNG
7 E:\STUDY\ORACLE\20231020_01_SCOTT.SQL
--------------------------------------------------
*/
--○ TBL_FILES 테이블을 조회하여
-- 다음과 같은 결과를 얻을 수 있도록 쿼리문을 구성한다.
/*
--------- ----------------------------------------
파일번호 파일명
--------- ----------------------------------------
1 SALES.DOC
2 PANMAE.XXLS
3 RESEARCH.PPT
4 STUDY.HWP
5 SQL.TXT
6 FLOWER.PNG
7 20231020_01_SCOTT.SQL
--------------------------------------------------
*/
-- A
SELECT FILENE"파일번호"
, SUBSTR(FILENAME, INSTR(FILENAME, '\', -1, 1)+1)
FROM TBL_FILES;
-- B
SELECT FILENE"파일번호"
, REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1, 1)-1))
FROM TBL_FILES;
-- 같이 풀이
SELECT FILENE "파일번호"
, FILENAME "경로포함파일명"
, SUBSTR(FILENAME, 16,9)
FROM TBL_FILES;
--==>> 이렇게하면 1번째만 적용이고 나머지는 엉망으로 나옴
/*
1 C:\AAA\BBB\CCC\SALES.DOC SALES.DOC
2 C:\AAA\PANMAE.XXLS XLS
3 D:\RESEARCH.PPT
4 C:\DOCUMENTS\STUDY.HWP UDY.HWP
5 C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT MP\HOMEWO
6 C:\SHARE\F\TEST\FLOWER.PNG \FLOWER.P
7 E:\STUDY\ORACLE\20231020_01_SCOTT.SQL \20231020
*/
SELECT FILENE "파일번호"
, REVERSE(FILENAME) "거꾸로된파일명"
FROM TBL_FILES;
--==>>
/*
1 COD.SELAS\CCC\BBB\AAA\:C
2 SLXX.EAMNAP\AAA\:C
3 TPP.HCRAESER\:D
4 PWH.YDUTS\STNEMUCOD\:C
5 TXT.LQS\KROWEMOH\PMET\STNEMUCOD\:C
6 GNP.REWOLF\TSET\F\ERAHS\:C
7 LQS.TTOCS_10_02013202\ELCARO\YDUTS\:E
*/
/*
1 COD.SELAS \CCC\BBB\AAA\:C → 최초 『\』 등장위치 : 10 → 1~9 추출
2 SLXX.EAMNAP \AAA\:C → 최초 『\』 등장위치 : 12 → 1~11 추출
3 TPP.HCRAESER \:D → 최초 『\』 등장위치 : 13 → 1~12 추출
4 PWH.YDUTS \STNEMUCOD\:C → 최초 『\』 등장위치 : 10 → 1~9 추출
5 TXT.LQS \KROWEMOH\PMET\STNEMUCOD\:C → 최초 『\』 등장위치 : 8 → 1~7 추출
6 GNP.REWOLF \TSET\F\ERAHS\:C → 최초 『\』 등장위치 : 10 → 1~9 추출
7 LQS.TTOCS_10_02013202 \ELCARO\YDUTS\:E → 최초 『\』 등장위치 : 22 → 1~21 추출
*/
SELECT FILENE "파일번호"
, FILENAME "경로포함파일명"
, REVERSE(FILENAME) "거꾸로된경로및파일명"
, SUBSTR(대상문자열, 추출시작위치, 최초 『\』 등장위치 - 1)"거꾸로된파일명"
FROM TBL_FILES;
SELECT FILENE "파일번호"
, FILENAME "경로포함파일명"
, REVERSE(FILENAME) "거꾸로된경로및파일명"
, SUBSTR(REVERSE(FILENAME), 1, 최초 『\』 등장위치 - 1)"거꾸로된파일명"
FROM TBL_FILES;
-- 최초 『\』 등장위치
-- → INSTR(REVERSE(FILENAME), '\', 1, 1)
-- → INSTR(REVERSE(FILENAME), '\', 1)
SELECT FILENE "파일번호"
, FILENAME "경로포함파일명"
, REVERSE(FILENAME) "거꾸로된경로및파일명"
, SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1) - 1)"거꾸로된파일명"
FROM TBL_FILES;
--==>>
/*
1 C:\AAA\BBB\CCC\SALES.DOC COD.SELAS\CCC\BBB\AAA\:C COD.SELAS
2 C:\AAA\PANMAE.XXLS SLXX.EAMNAP\AAA\:C SLXX.EAMNAP
3 D:\RESEARCH.PPT TPP.HCRAESER\:D TPP.HCRAESER
4 C:\DOCUMENTS\STUDY.HWP PWH.YDUTS\STNEMUCOD\:C PWH.YDUTS
5 C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT TXT.LQS\KROWEMOH\PMET\STNEMUCOD\:C TXT.LQS
6 C:\SHARE\F\TEST\FLOWER.PNG GNP.REWOLF\TSET\F\ERAHS\:C GNP.REWOLF
7 E:\STUDY\ORACLE\20231020_01_SCOTT.SQL LQS.TTOCS_10_02013202\ELCARO\YDUTS\:E LQS.TTOCS_10_02013202
*/
SELECT FILENE "파일번호"
, FILENAME "경로포함파일명"
, REVERSE(FILENAME) "거꾸로된경로및파일명"
, SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1) - 1)"거꾸로된파일명"
, REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1) - 1)) "파일명"
FROM TBL_FILES;
--==>>
/*
1 C:\AAA\BBB\CCC\SALES.DOC COD.SELAS\CCC\BBB\AAA\:C COD.SELAS SALES.DOC
2 C:\AAA\PANMAE.XXLS SLXX.EAMNAP\AAA\:C SLXX.EAMNAP PANMAE.XXLS
3 D:\RESEARCH.PPT TPP.HCRAESER\:D TPP.HCRAESER RESEARCH.PPT
4 C:\DOCUMENTS\STUDY.HWP PWH.YDUTS\STNEMUCOD\:C PWH.YDUTS STUDY.HWP
5 C:\DOCUMENTS\TEMP\HOMEWORK\SQL.TXT TXT.LQS\KROWEMOH\PMET\STNEMUCOD\:C TXT.LQS SQL.TXT
6 C:\SHARE\F\TEST\FLOWER.PNG GNP.REWOLF\TSET\F\ERAHS\:C GNP.REWOLF FLOWER.PNG
7 E:\STUDY\ORACLE\20231020_01_SCOTT.SQL LQS.TTOCS_10_02013202\ELCARO\YDUTS\:E LQS.TTOCS_10_02013202 20231020_01_SCOTT.SQL
*/
SELECT FILENE "파일번호"
, REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME), '\', 1) - 1)) "파일명"
FROM TBL_FILES;
--==>>
/*
1 SALES.DOC
2 PANMAE.XXLS
3 RESEARCH.PPT
4 STUDY.HWP
5 SQL.TXT
6 FLOWER.PNG
7 20231020_01_SCOTT.SQL
*/
■ LPAD(), RPAD()
- LPAD() : Byte 확보하여 왼쪽부터 문자로 채우는 기능을 가진 함수
- RPAD() : Byte 확보하여 오른쪽부터 문자로 채우는 기능을 가진 함수
--○ LPAD()
--> Byte 를 확보하여 왼쪽부터 문자로 채우는 기능을 가진 함수
SELECT 'ORACLE' "COL1"
, LPAD('ORACLE', 10, '*') "COL2"
FROM DUAL;
--> ① 10Byte 공간을 확보한다. → 두 번째 파라미터 값에 의해
-- ② 확보한 공간에 'ORACLE' 문자열을 담는다. → 첫 번째 파라미터 값에 의해
-- ③ 남아있는 Byte 공간을 왼쪽부터 세 번째 파라미터 값으로 채운다.
-- ④ 이렇게 구성된 최종 결과값을 반환한다.
--==>> ORACLE ***ORACLE
--○ RPAD()
--> Byte 를 확보하여 오른쪽부터 문자로 채우는 기능을 가진 함수
SELECT 'ORACLE' "COL1"
, RPAD('ORACLE', 10, '*') "COL2"
FROM DUAL;
--> ① 10Byte 공간을 확보한다. → 두 번째 파라미터 값에 의해
-- ② 확보한 공간에 'ORACLE' 문자열을 담는다. → 첫 번째 파라미터 값에 의해
-- ③ 남아있는 Byte 공간을 오른쪽부터 세 번째 파라미터 값으로 채운다.
-- ④ 이렇게 구성된 최종 결과값을 반환한다.
--==>> ORACLE ***ORACLE
■ TRIM(), LTRIM(), RTRIM()
- 첫 번째 파라미터 값에 해당하는 문자열을 대상으로
- 왼쪽 / 오른쪽부터 연속적으로 등장하는 두 번째 파라미터 값에서 지정한 글자와
- 같은 글자가 등장할 경우 이를 제거하고 남은 결과값 반환
- 단, 완성형으로 처리되지 않음!
- TRIM은 양쪽 다 제거~!!!!
--○ LTRIM()
SELECT 'ORAORAORAORACLEORACLE' "COL1" -- 오라 오라 오라 오라클 오라클
, LTRIM('ORAORAORAORACLEORACLE', 'ORA') "COL2"
, LTRIM('AAAAAAAAAORACLEORACLE', 'ORA') "COL3"
, LTRIM('ORAORAoRAORACLEORACLE', 'ORA') "COL4"
, LTRIM('ORAORA ORAORACLEORACLE', 'ORA') "COL5"
, LTRIM(' ORACLE', ' ') "COL6" -- 왼쪽 공백 제거
, LTRIM(' ORACLE') "COL7" -- 두 번째 파라미터 생략
FROM DUAL;
/*
ORAORAORAORACLEORACLE
CLEORACLE
CLEORACLE
oRAORACLEORACLE
ORAORACLEORACLE
ORACLE
ORACLE
*/
--> 첫 번째 파라미터 값에 해당하는 문자열을 대상으로
-- 왼쪽부터 연속적으로 등장하는 두 번째 파라미터 값에서 지정한 글자와
-- 같은 글자가 등장할 경우 이를 제거하고 남은 결과값을 반환한다.
-- 단, 완성형으로 처리되지 않는다.
SELECT LTRIM('김이신이김김이이신김김김이김이김박이김신', '김신이') "COL1"
FROM DUAL;
--==>> 박이김신
--○ RTRIM()
--> 첫 번째 파라미터 값에 해당하는 문자열을 대상으로
-- 오른쪽부터 연속적으로 등장하는 두 번째 파라미터 값에서 지정한 글자와
-- 같은 글자가 등장할 경우 이를 제거하고 남은 결과값을 반환한다.
-- 단, 완성형으로 처리되지 않는다.
--※ 추가
--○ TRIM()
SELECT TRIM(' TEST ') "COL1"
, LTRIM(' TEST ') "COL2"
, RTRIM(' TEST ') "COL3"
FROM DUAL;
--==>> TEST TEST TEST
■ TRANSLATE(), REPLACE()
- TRANSLATE : 1 : 1로 바꿔줌
- REPLACE : 첫 번재 파라미터에서 두 번째 파라미터 값을 찾아 세 번째 파라미터 값으로 바꿔줌
--○ TRANSLATE()
--> 1 :1 로 바꿔준다.
SELECT TRANSLATE('MY ORACLE SERVER'
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, 'abcdefghijklmnopqrstuvwxyz') "COL1"
FROM DUAL;
--==>> my oracle server
SELECT TRANSLATE('010-1234-5678'
, '0123456789'
, '공일이삼사오육칠팔구') "COL1"
FROM DUAL;
--==>> 공일공-일이삼사-오육칠팔
--○ REPLACE()
SELECT REPLACE('MY ORACLE SERVER ORAHOME', 'ORA', '오라') "COL1"
FROM DUAL;
--==>> MY 오라CLE SERVER 오라HOME