day24) [Oracle]정보 추출, 가공을 통해 원하는 데이터 얻기(INSTR, SUBSTR,REPLACE,LPAD,RTRIM,TRIM)

[테이블 생성]

create table test(
   tpk int primary key,
   tname varchar(100) not null,
   tnumber varchar(100),
   temail varchar(100)
);
insert into test values((select (nvl(max(tpk),0))+1 from test), '홍길동', '010-1034-3434', 'qkqkqk1004@nate.com');
insert into test values((select (nvl(max(tpk),0))+1 from test), '김두루미', '070-9999-8888', 'kimbird@hanmail.net');
insert into test values((select (nvl(max(tpk),0))+1 from test), '티모', '02-911-2828', 'timotimo@gmail.com');

 

[INSTR]

-입력된 문자열이 일치하면 그 첫번째 인덱스값을 반환해주고, 일치하는 문자가 없으면 0을 반환 해주는 함수

--기본 사용법
select instr(문자열,찾을문자,시작지점,몇번째) from test;

--찾는값이 없으면 0을 리턴한다.
select instr(tnumber,0,1,2) from test; 	--3,3,0

--시작지점이 -1이면 뒤에서 부터 찾는다.(INDEX는 앞에서부터) 
select instr(tnumber,0,-1,2) from test; --3,1,0

--몇번째 값의 DEFAULT는 1이다			
select instr(tnumber,9,6) from test;	--0,6,0

--시작지점의 DEFAULT는 1이다
select instr(tnumber,'-') from test;	--4,4,3

 

[SUBSTR]

-문자를 잘라 추출하는 함수

--기본 사용법
select SUBSTR(문자열,시작지점,길이) from test;

select SUBSTR(tnumber,2,5) from test; 				--10-10, 70-99, 2-911

--INSTR로 -의 위치를 알아 그 앞의 문자를 반환.
select SUBSTR(tnumber,1,INSTR(tnumber,'-')-1) from test;	--010, 070, 02

--길이를 입력하지 않을 시, 시작지점으로 부터 끝까지 반환한다.
select SUBSTR(tnumber,INSTR(tnumber,'-',-1)+1) from test;	--3434, 8888, 2828

--@를 기준으로 뒤의 문자열을 반환
select substr(temail,instr(temail,'@')+1) from test;		--nate.com, hanmail.com, gmail.com

 

[REPLACE]

특정 문자열을 치환하거나 제거하기 위한 함수.

--기본 사용법
select replace(문자열, 대상이 되는 문자,원하는 문자) from test;

--이름의 2번째 문자를 *로 바꿔준다.
select replace(tname, substr(tname,2, 1),'*') from test;	--홍*동, 김*루미, 티*

--전화번호의 모든 - 를 제거한다.
select replace(tnumber, '-','') from test;		--01010343434, 07099998888, 029112828

--이메일의 4번째 글자부터 '******'로 치환한다.
select replace(temail, substr(temail,4), '******') from test; --qkq******, kim******, tim******

-- SUBSTR(전화번호의, 첫번째 -의 인덱스 번호+1, ((뒤에서 첫번째 -1의 인덱스 번호 -1) - (첫번째 -의 인덱스 번호)))
-- 을 '****'로 바꾼다
-->SUBSTR을 통해 -사이의 번호 4자리를 알아내고, 이를 '****'로 치환 
select replace(tnumber, substr(tnumber,INSTR(tnumber,'-')+1,((INSTR(tnumber,'-',-1)-1)-INSTR(tnumber,'-',1))),'****') from test;
--010-****-3434, 070-****-8888, 02-****-2828

 

[LPAD]

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

--기본 사용법
LPAD(값, 총 문자길이, '채움문자')

--30의 공간을 확보한 후, 각 이메일 값을 오른쪽에 채운후, 남은 공간을 왼쪽부터 빈공간으로 놔둔다
select LPAD(temail,30) from test;

--30의 공간을 확보한 후, 각 이메일 값을 오른쪽에 채운후, 남은 공간을 왼쪽부터 *로 채운다
select LAPD(temail,30 ,*) from test;

--30의 공간을 확보한 후, 각 이메일 값을 왼쪽에, 남은 공간을 오른쪽 부터 *로 채운다
select RPAD(temail,30 ,*) from test;

 

 

[문제]

1. 이름의 첫글자와 마지막 글자를 제외한 모든 문자열을 *로 치환

2. 이메일의 5번째 글자부터 @전까지 모든 문자열 *로 치환

select (substr(tname,1,1)) || (lpad('*' ,length(tname)-2,'*')) || (substr(tname,-1,1)) as tnamehide from test;
select (substr(temail,1,4) || lpad('*', instr(temail,'@')-5,'*') || substr(temail,instr(temail, '@')) ) from test;