SQL 단일행 함수 - 문자함수 Substr( 문자에서 특정단어 or 철자 추출 )

substr : 문자에서 특정 철자 추출하기

문자에서 지정된 길이의 문자열을 추출할 수 있다. 

substr (' 단어나 컬럼명' , 뽑아올 순서의 단어 순서, 철자의 수  )

• 특정 조건을 사용해서 출력 할때
• where substr(                     ,                     ,              ) = 
• 참고로 substr 함수는 문자 이기 때문에 = 뽑을 특정 단어도 문자 함수(to_char)로 추출 할 것 '' 싱클 쿼테이션 마크로 두르기

예제 17 : smi 만 출력하기

select substr('s m I t h' , 1, 3, )

설명 (' smith' , 첫번째철자인 1 , 1번째부터 3개를 뽑겠다.  )

substr(ename, 2 ) 이런식으로 2 다음에 다음 수를 넣지 않고 괄호를 닫으면 끝까지 읽는 것 입니다.

from dual;

from 절에 emp 나 dept 쓰면 그 행수만큼 smith 가 나오기 때문에 dual 을 사용해 주었다.

dual? 가상의 테이블 (엄밀히 말하면 가상의 테이블은 아니지만 일단 가정은)

문제 : (단어말고 컬럼명 사용해서 추출해 보기 )사원테이블에서 이름을 출력하고, 그옆에 이름의 첫번째 철자만 출력

select ename, substr(ename, 1, 1)

from emp

응용 : 81년도에 입사한 사원들의 이름과 입사일 뽑으세요

select ename, hiredate

from emp

where substr(hire date,1,2) = '81';

-> 81년도에 입사한 사람을 뽑는 검색 조건이므로 where 절에 기술한다. 

문제 : 이름 입사일, 입사한 달을 출력

select ename, hiredate, substr(hire date, 4, 2)

from emp;

→ 참고로 hiredate 에 있는 / 도 숫자로 세야한다.

문제 : 이름의 첫번째 철자를 출력하는데 소문자로 출력

select ename, lower(substr(ename, 1, 1))

from emp

설명 : lower 함수로 전체를 둘러주면 된다~

문제 : 성이 김씨인 학생들의 이름과 주소를 출력

select ename, address

from emp14

where substr(ename, 1, 1) = '김';

 

문제  : 이름, 주소, 주소의 첫번째 공백 전까지의 글자를 출력

select ename, address, regexp_substr(address, '[^ ]+', 1, 1) 
from emp14;

→ substr 은 사용 어려움, 좀더 발전된 형태의 데이터 전처리용인

regexp_substr (정규식 함수) 을 사용해준다.

→ 경기도 수원시 팔달구 중 경기도 가 여러개(+) 중 첫번째의 첫번째 여러개 이기때문에

      ( address,                                 '[^ ]+'            1,             1,) 의 식이 나온다. 

---

regular expression (정규표현식) substr

1. ^ 의 뜻은 NOT
2. • 의 뜻은 ^ 다음에 해당되는 철자가 여러개 라는 뜻
3. 그래서 [^ ]+ 의뜻은 공백이 아닌게 여러개 있는것
4. → 만약에 + 를 하지 않게 되면 단어를 묶어서 인식 하지 못하고,

'경','기','도' 이런식으로 한글자로 인식하게 된다.

---

or

select ename, address, substr( address, 1, instr(address, ' ')-1) 
from emp14;

→ address 를 1번째부터 읽어서 instr에서 공백 ' '의 순서를 추출하고 -1 (그순서 전까지) 뽑겠다는 해석

문제 181 : 위의 문제에서, 잘라낸 주소를 order by 로 asc 하게 출력

select ename, address, regexp_substr(address, '[^ ]+', 1, 1)  
from emp14
order by 3 asc;

문제 : 서울특별시 → 서울시 , 인천광역시 → 인천시 이름과 잘라낸 주소를 출력하시오.

select ename, 
       decode (
               regexp_substr(address,'[^ ]+', 1, 1), '서울특별시', '서울시',
                                                     '인천광역시','인천시',
                                                     regexp_substr(address,'[^ ]+', 1, 1) 
               )as "주소" 
from emp14;

해석, 이름을 뽑고, 그옆에 주소를 뽑을 건데 잘라낸 주소 이름중에서 서울특별시는 서울시로, 인천광역시는 인천시로 출력하고 나머지는 잘라낸 주소 이름들중 1번째 단어부터 1번째까지 뽑은 후 별칭은 주소라고 한다.

→ 설명 : 위의 SQL 로 점도 다른 결과를 보려고 잘라낸 주소별로 각각 파티션해서 나이가 높은 순서대로 순위를 부여하는 SQL 까지 작성하게 되면 SQL 이 더 복잡해지게 된다.

좀더 심플하게 SQL 을 작성하기 위해서 , 아니면 이 쿼리식을 자주쓰게 될것 같다면 위의 SQL 의 결과를 보는 VIEW 를 생성한다. 

create view emp14_address
as 
select ename, decode (
                      regexp_substr(address,'[^ ]+', 1, 1), '서울특별시', '서울시', 
                                                            '인천광역시','인천시',
                                                            regexp_substr(address,'[^ ]+', 1, 1) 
                      )as address2
from emp14
ORDER BY 2 ASC;

→ 후에 잘 만들었는지 조회 한다.

select * 
from emp14_address;

→ 복잡한 쿼리문이 바로 조회된다.

---

view ? select 문장을 view 로 만들게 되면 검색했을때 보인다.

복잡한 SQL 을 단순하게 검색하고 싶을 때 VIEW 를 만들어서 사용합니다.

만약, view 문장에 더 추가하고 싶다면,

create or replace view 문장으로 실행하면 기존 view table 이 삭제되지않고 replace 되어 변경된다,

주의할 점은 order by 절의 순서를 잘 확인해 주어야한다.

→ select절이 변경 될경우 숫자로 입력한 order by 절때문에 쿼리값이 이상해 질 수 있음.

---