25. (데이터베이스) SQL 문자열 함수 연습 문제

문제 : 전화번호 가운데 자리 추출하기

(1) 가운데 자리 3개 → 찾아서 출력 (055일 경우)

(2) 가운데 자리 3개 → 찾아서 출력 (02일 경우)

(3) 가운데 자리 3개를 동적으로 찾아서 출력

• instr() 로 동적으로 찾기

(4) 가운데 자리 3개 → ***로 변경

• replace() 사용

풀이

(1) 가운데 자리 3개 → 찾아서 출력 (055일 경우)

select substr('055)381-2158',5,3);

(2) 가운데 자리 3개 → 찾아서 출력 (02일 경우)

select substr('02)381-2158',4,3);

(3) 가운데 자리 3개를 동적으로 찾아서 출력

• instr() 로 동적으로 찾기

select substr(
'055)381-2158',
instr('055)381-2158',')')+1,
instr('055)381-2158','-')-instr('055)381-2158',')')-1);

select substr(
'02)381-2158',
instr('02)381-2158',')')+1,
instr('02)381-2158','-')-instr('02)381-2158',')')-1);

(4) 가운데 자리 3개 → ***로 변경

select replace(
'02)381-2158',
substr('02)381-2158', instr('02)381-2158',')')+1, instr('02)381-2158','-')-instr('02)381-2158',')')-1),
'***');

select replace(
'051)381-2158',
substr('051)381-2158', instr('051)381-2158',')')+1, instr('051)381-2158','-')-instr('051)381-2158',')')-1),
'***');

select replace(
'051)3812-2158',
substr('051)3812-2158', instr('051)3812-2158',')')+1, instr('051)3812-2158','-')-instr('051)3812-2158',')')-1),
'****');

(5) *개수 동적으로 변경

select replace(
'02)381-2158',
substr('02)381-2158', instr('02)381-2158',')')+1, instr('02)381-2158','-')-instr('02)381-2158',')')-1),
repeat('*', instr('02)381-2158','-')-instr('02)381-2158',')')-1));

select replace(
'051)381-2158',
substr('051)381-2158', instr('051)381-2158',')')+1, instr('051)381-2158','-')-instr('051)381-2158',')')-1),
repeat('*', instr('051)381-2158','-')-instr('051)381-2158',')')-1));

select replace(
'051)3812-2158',
substr('051)3812-2158', instr('051)3812-2158',')')+1, instr('051)3812-2158','-')-instr('051)3812-2158',')')-1),
repeat('*', instr('051)3812-2158','-')-instr('051)3812-2158',')')-1));

(6) 최종

select name, tel, replace(
tel,
substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1),
repeat('*', instr(tel,'-')-instr(tel,')')-1)) as 'tel1'
from student;

select name, tel, replace(
tel,
substr(tel, instr(tel,')')+1, instr(tel,'-')-instr(tel,')')-1),
rpad('', instr(tel,'-')-instr(tel,')')-1,'*')) as 'tel1'
from student;

정답

-- rpad, instr, substr

-- 1. step1 -> 노가다 (변수, 상수)
-- 055)399-2322
select replace('055)399-2322', '399', '***');
-- 02)2232-4444
select replace('02)2232-4444', '2232', '****');

-- 2. step2 -> 가운데 자리 찾기
select substr('055)399-2322', 5, 3);
select substr('02)2232-4444', 4, 4);

-- 3. step3 -> 가운데 자리 시작 위치 = ')'자리 +1
select instr('055)399-2322', ')')+1;
select instr('02)2232-4444', ')')+1;

-- 4. step4 -> step3을 step2에 넣기
select substr('055)399-2322', instr('055)399-2322', ')')+1, 3);

-- 5. step5 -> 가운데 자리 개수 = '-'자리 - ')'자리 - 1
select instr('055)399-2322', ')'); -- 4
select instr('055)399-2322', '-'); -- 8

select instr('02)2232-4444', ')'); -- 3
select instr('02)2232-4444', '-'); -- 8

select instr('055)399-2322', '-') - instr('055)399-2322', ')') - 1;

-- 6. step6 -> step5를 step4에 넣기
select substr('055)399-2322', instr('055)399-2322', ')')+1, instr('055)399-2322', '-')-instr('055)399-2322', ')')-1);
select substr('02)2232-4444', instr('02)2232-4444', ')')+1, instr('02)2232-4444', '-')-instr('02)2232-4444', ')')-1);

-- 7. step7 -> step6를 step1에 넣기
-- 가운데 자리를 ***로 바꾸는 친구
select replace(
'055)399-2322',
substr('055)399-2322', instr('055)399-2322', ')')+1, instr('055)399-2322', '-')-instr('055)399-2322', ')')-1),
'***');
select replace(
'02)2232-4444',
substr('02)2232-4444', instr('02)2232-4444', ')')+1, instr('02)2232-4444', '-')-instr('02)2232-4444', ')')-1),
'****');

-- 8. step8 -> 별 개수 3개? 4개?
select rpad('', instr('055)399-2322', '-') - instr('055)399-2322', ')')-1, '*');
select rpad('', instr('02)2232-4444', '-') - instr('02)2232-4444', ')')-1, '*');

-- 9. step9 -> step8을 step7에 넣기
select replace(
'055)399-2322',
substr('055)399-2322', instr('055)399-2322', ')')+1, instr('055)399-2322', '-')-instr('055)399-2322', ')')-1),
rpad('', instr('055)399-2322', '-') - instr('055)399-2322', ')')-1, '*'));
select replace(
'02)2232-4444',
substr('02)2232-4444', instr('02)2232-4444', ')')+1, instr('02)2232-4444', '-')-instr('02)2232-4444', ')')-1),
rpad('', instr('02)2232-4444', '-') - instr('02)2232-4444', ')')-1, '*'));

-- 10. step10 -> 테이블에서 데이터 가져오기
select replace(
tel,
substr(tel, instr(tel, ')')+1, instr(tel, '-')-instr(tel, ')')-1),
rpad('', instr(tel, '-') - instr(tel, ')')-1, '*'))
from student;