[Oracle] 오라클 #8, 오라클 함수(문자열 함수)

문자열함수

1. upper(),lower(),initcap()

- varchar2 upper(컬럼명)

ex)

>> select 'studentName',upper('studentName'),lower('studentName') from dual;

>> select * from employees;

>> select upper(first_name),lower(last_name) from employees;

ex) 검색 예제

사용자의 검색어 입력 >> "an"

select * from employees

    --where first_name like'D%';

    --where first_name like'%an%';

    where upper(first_name) like'%an%';

오라클 특징!

- 오라클 키워드는 대소문자 구분 안한다.

- 오라클 데이터는 대소문자 구분 한다.

- 오라클은 문법을 파싱(해석)하는 과정에서 전부 대분자로 변환시킨다.

2. substr

- 문자열 추출

- 자바로치면 substring

- varchar2 substr(컬럼명,시작위치,개수)

ex)

>> select '가나다라마바사아자차카타파하' from dual;                |  > 가나다라마바사아자차카타파하

>> select substr('가나다라마바사아자차카타파하',3,5) from dual;  |  > 다라마바사

--제로베이스인덱스가 아니다. 1부터 인덱스 시작한다는걸 명심하자!!!*****

3. length()

-문자열 길이

-number length(컬럼명)

ex)

>> select email,length(email) from employees;   -- 이메일과 이메일의 길이를출력

>> select email,length(email),avg(length(email)) from employees; --ORA-00937: not a single-group group function

-> 이건 이메일은 쫘르륵 출력되는데 avg함수는 단일값만 출력하기때문에 테이블의 꼬임이 생긴 것임. 이렇게 출력하면 안됨.

>> select trunc(avg(length(email)),2) from employees;

-> 길이 평균을 소수 2째짜리까지만 표현

4. instr()

- 자바로 치면 : indexOf()

- 검색어의 위치를 반환

- number instr(컬럼명,검색어)

- number instr(컬럼명,검색어,시작위치)

ex)

>> select instr('안녕하세요. 홍길동입니다.','홍길동'),instr('안녕하세요안. 홍길동입니다.','안') from dual; --찾으면 인덱스반환

>> select instr('안녕하세요. 홍길동입니다.','홍길동'),instr('안녕하세요안. 홍길동입니다.','티') from dual; --못찾으면 0반환

5. lpad(), rpad()

- left padding, right padding

- varchar2 lpad(컬럼,갯수,문자)

ex)

>> select first_name, lpad(first_name,20,'★')from employees;

>> select first_name, rpad(first_name,20,'★')from employees;

-> first_name의 값까지 합해서 총 20개의 문자가 들어갈 공간을 만드는데 lpad는 남는 공간의 왼쪽으로 '★'로 채우고, rpad는 그 오른쪽으로 '★'로 채운다.

7. trim(),ltrim(),rtrim()

- varchar2 trim(컬럼명)

- trim : 양옆 공백 둘다 제거

- ltrim : 왼쪽 공백만 제거

- rtrim : 오른쪽 공백만을 제거

ex)

>> select '    홍길동',trim('    홍길동'),ltrim('     홍길동    '),rtrim('  홍길동 ') from dual;

ex) char vs varchar2(고정길이 vs 가변길이)

create table tblString(

    name1 char(50) not null,

    name2 varchar(50) not null

);

insert into tblString (name1,name2) values('홍길동','홍길동');

>> select * from tblString where name1 = '홍길동';  -- char  

-> char형을 조건절에 사용하면 자동으로 trim()실행.

>> select * from tblString where name2 = '홍길동';  -- varchar2

>> select * from tblString where rtrim(name1) = name2;

>> select length(name1),length(name2) from tblString; 

-> 44, 3 나옴

8. replace()

- 문자열 치환 함수

- varchar2 replace(컬럼명, 찾을 문자열, 바꿀 문자열)

ex)

>> select '홍길동',replace('홍길동','홍','김') from dual;

>> select name,replace(replace(replace(replace(replace(continent,'AS','아시아'),'EU','유럽'),'SA','아메리카'),'AF','아프리카'),'AU','호주') from tblcountry;

9. decode()**************

- 문자열 치환

- replace()와 유사

- varchar2 decode(컬럼명,찾을문자열,바꿀문자열,[찾을문자열,바꿀문자열],[찾을문자열,바꿀문자열])

- 못찾으면 null반환 *************

ex)

--총무부의 직원은 몇명??

select count(*) from tblinsa where buseo = '총무부';    -> 총무부원의 수

--영업부의 직원은 몇명??

select count(*) from tblinsa where buseo = '영업부';    -> 영업부원의 수

--홍보부의 직원은 몇명??

select count(*) from tblinsa where buseo = '홍보부';    -> 홍보부원의 수

위의 3가지 질문과 동일한 질문 + 결과를 1개의 테이블로 받고싶다.

[총무부] [영업부] [홍보부]

     7       16        6

select 

    count(decode(buseo,'총무부',1)) as "총무부",    -> 여기서 총무부를 1로 바꾸고 1을 세면 -> 총무부인 사람들

    count(decode(buseo,'영업부',0)) as "영업부",    -> 마찬가지

    count(decode(buseo,'홍보부',0)) as "홍보부"     -> 마찬가지

from tblinsa;

10. nvl()

- null함수, null value

- object nvl(컬럼명, 대체값)

- 해당컬럼이 null이면 대체값을 반환하고, not null이면 원래 컬럼값을 반환한다.

es)

create table tblTest(

    seq number primary key,

    name varchar2(30) not null,

    height number null

);

insert into tblTest values(1, '홍길동',170);

insert into tblTest values(2, '아무개',160);

insert into tblTest values(3, '임꺽정',150);

insert into tblTest values(4, '하하하',null);

tblTest에서 하하하의 height 컬럼은 null 값이다. height를 기재하지 않은 사람은 기본값이 100이라고 한다!!

>> select seq, name, nvl(height, 100) from tblTest;