SQL class #2 SUBSTR

SQL class #2 WHERE

1. SUBSTR 과 INSTR

 1  select name,tel,substr(tel,1,instr(tel,')',1,1))
  2  from student
  3* where deptno1=101
SCOTT>/

NAME       TEL             SUBSTR(TEL,1,INSTR(TEL,')',1,1
---------- --------------- ------------------------------
서진수     055)381-2158    055)
김신영     055)333-6328    055)
일지매     02)6788-4861    02)
이윤나     055)278-3649    055)

INSTR(TEL,')',1,1 은 TEL 칼럼의 데이터중 ')'가 첫글자부터 처음나오는 위치를 숫자로 리턴해주고,

그러면,

SUBSTR(TEL,1,4) 가 되는데 (서진수 데이터 조건일시)

TEL 데이터 처음부터 4번째까지 데이터를 뽑아내는 쿼리 입니다.

2. SUBSTR 과 INSTR (2)

  1  select name,tel,substr(tel,1,instr(tel,')',1,1)-1)
  2  from student
  3* where deptno1=101
SCOTT>/

NAME       TEL             SUBSTR(TEL,1,INSTR(TEL,')',1,1
---------- --------------- ------------------------------
서진수     055)381-2158    055
김신영     055)333-6328    055
일지매     02)6788-4861    02
이윤나     055)278-3649    055

INSTR 은 내용이 위와 같은 내용이고, SUBSTR 역시 다 똑같지만

뒤에, SUBSTR(TEL,1,INSTR(TEL,')',1,1)-1 이 있는데,

이는 INSTR가 리턴한 4 에서 -1을 하라는 의미 입니다. 즉, 리턴값은 3이 됩니다.

그래서 출력을 보면 위에선 ')' 까지 나왔지만, 이번에는 숫자만 나오게 됩니다.

3. LPAD

SCOTT>select lpad(dname,10,'1234') "LPAD 연습"
  2  from dept2
  3  ;

LPAD 연습
--------------------
1234사장실
경영지원부
재무관리팀
1234총무팀
1234기술부
123H/W지원
123S/W지원
1234영업부
영업기획팀
123영업1팀
123영업2팀
123영업3팀
123영업4팀

13 rows selected.

LPAD 는 LPAD(DNAME,10,'1234') 쿼리를 풀어보면,

DNAME 칼럼의 데이터를 총 10 byte로 설정을 하고 기존데이터를 오른쪽 정렬을 한 뒤,

'1234'를 왼쪽부터 남는 자릿수만큼 채워넣는 쿼리 입니다.

이때 주의하실점은 데이터의 길이를 설정할때, 지정한 칼럼의 데이터의 가장 큰 크기보다

같거나 크게 잡아야 한다는겁니다.

4. RPAD

 1  select rpad(dname,10,'1234567890') "RPAD 연습"
  2*  from dept2
SCOTT>/

RPAD 연습
--------------------
사장실1234
경영지원부
재무관리팀
총무팀1234
기술부1234
H/W지원123
S/W지원123
영업부1234
영업기획팀
영업1팀123
영업2팀123
영업3팀123
영업4팀123

13 rows selected.

RPAD 는 LPAD를 거꾸로 생각하시면 됩니다. RPAD(DNAME,10,'1234567890') 에서

DNAME 칼럼의 데이터를 좌측으로 정렬한 뒤, 10 byte의 자리를 설정하고 뒤 부터 '1234567890'

을 남은자리에 채워넣습니다.

5. RPAD 와 SUBSTR, LENGTHB

 1  select rpad(dname,10,
  2             substr('1234567890',lengthb(dname)+1)) "RPAD 연습"
  3*  from dept2
SCOTT>/

RPAD 연습
--------------------
사장실7890


총무팀7890
기술부7890
H/W지원890
S/W지원890
영업부7890

영업1팀890
영업2팀890
영업3팀890
영업4팀890

13 rows selected.

RPAD 내부에 SUBSTR을 써서 약간의 변형을 주는 방법 입니다.

LENGTHB(DNAME)+1 은 DNAME칼럼의 byte 수를 리턴해서 +1 을 합니다.

그러면 SUBSTR('1234567890',7) 이 되는데, 그러면 '1234567890' 에서 7번째 글자부터

마지막까지 출력을 하게 되는겁니다.

그러면 위와같은 출력을 얻을수 있습니다.

6. REPLACE

  1  select replace(name,substrb(name,3,2),'#') "리플레이스연습"
  2  from student
  3* where deptno1=101
SCOTT>/

리플레이스
----------
서#수
김#영
일#매
이#나

REPLACE란 말그대로 기존의 데이터를 특정 문자로 바꿔주는 함수입니다.

SUBSTRB(NAME,3,2) 는 3번째 byte 부터 2byte 닌깐 '서진수' 라는 데이터에서 

'진' 이라는 글씨를 리턴합니다.

그러면, REPLACE(NAME,'진','#') 이라는 쿼리가 됩니다.

NAME 칼럼의 데이터에서 '진'이라는 글씨는 '#'으로 바뀌게 됩니다.

7. REPLACE (2)

 1  select name, tel, replace(tel,substr(tel,instr(tel,')',1)+1,3),'###') "REPLACE"
  2  from student
  3* where deptno1=102
SCOTT>/

NAME       TEL             REPLACE
---------- --------------- ---------------------------------------------
서재수     051)426-1700    051)###-1700
신은경     051)418-9627    051)###-9627
김진욱     055)488-2998    055)###-2998
김주현     055)423-9870    055)###-9870

조금 복잡해 보이지만, 하나하나 차근히 풀어보면, INSTR(TEL,')',1)+1 은 5를 리턴 합니다.

(서재수 데이터 경우), 그러면, SUBSTR(TEL,5,3) 값이 되고, 이는 TEL 칼럼의 데이터의

5번째 부터 3자리를 리턴하게 되는데 이는 426이 됩니다.

다시 REPLACE(TEL,'456','###') 이 됩니다. 결국 '456' 이 '###" 으로 바뀌게 됩니다.

이상으로 두번째 시간이 끝났습니다. ^^*

짧았지만, 도움이 되었기를 바랍니다.

3번째 시간으로 돌아오겠습니다!!

By.sTricky