오라클(SQL) 함수2(문자열 함수)

1. upper(), lower,initcap() 

- 대소문자 변경

- varchar2 upper(컬럼명)

ex)

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

2. like 

- 검색

**

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

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

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

ex) 검색어 사용자 입력 : an

- select * from employees

where uppder(first_name) like '%' || uppder('an')||'%'; 

-     where first_name like 'D%;  => D로 시작하는 모든 단어

-     where first_name like '%an%'; =? an이 포함되어 있는 단어

3. substr

- 문자열 추출

- 자바 : substring

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

- varchar2 substr(컬럼명, 시작위치) : 끝까지

- SQL은 서수를 1부터 시작(******)

ex)

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

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

ex)

select name,ssn,

    '19'||substr(ssn,1,2) as "태어난 년도",

    substr(ssn,3,2) as "태어난 월",

    substr(ssn,8,1) as "성별",

    case

        when substr(ssn,8,1) = '1' then '남자'

        when substr(ssn,8,1) = '2' then '여자'

    end as "성별",

    case

        when substr(ssn,3,2) >=3 and substr(ssn,3,2) <=5 then '봄'

        when substr(ssn,3,2) >=6 and substr(ssn,3,2) <=9 then '여름'

        when substr(ssn,3,2) >=10 and substr(ssn,3,2) <=11 then '가을'

        else '겨울'

    end as "계절"

from tblinsa;

4.length()

- 문자열 길이

- number length(컬럼명)

ex)

select email,length(email) from employees;

select round(avg(length(email))) from employees;

5. instr()

- 자바 : indexOf()

- 검색어의 위치를 반환

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

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

- 못찾으면 0을 반환

ex)

select instr('안녕하세요. 홍길동님','홍길동') from dual; =>8(위치)

select instr('안녕하세요. 홍길동님. 잘가세요. 홍길동님','홍길동',11) from dual; 

select instr('안녕하세요. 홍길동님','아무개') from dual; 

6. lpad(),rpad()

- left padding, right padding

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

- 총문자를 확보하고 (갯수), 채워넣을 문자(문자), 컬럼

select first_name, lpad(first_name,20,'☆'), rpad(first_name,20,'☆') from employees;  

=>총 20개의 공간을 가지고 왼쪽부터 스타로 채운후 글자를 채운다.

=>총 20개의 공간을 가지고 오른쪽부터 스타로 채운후 글자를 채운다.

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

- varchar2 trim(컬럼명)

- 공백제거

8. replace()

- 문자열 치환 함수

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

ex)

select '홍길동', replace('홍길동','홍','김') from dual; => '홍'을 '김'으로 치환

9. decode()

- 문자열 치환 함수

- replace() 유사

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

ex)

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

=> AS -> 아시아 / EU -> 유럽 / AF->아프리카 / SA -> 아메리카 / AU -> 호주

ex)--tblinsa. '김' 몇명? '이','박','정','최'

select name from tblinsa;

select

    count(decode(substr(name,1, 1),'김','1')) as "김씨",

    count(decode(substr(name,1, 1),'이','1')) as "이씨",

    count(decode(substr(name,1, 1),'박','1')) as "박씨",

    count(decode(substr(name,1, 1),'정','1')) as "정씨",

    count(decode(substr(name,1, 1),'최','1')) as "최씨"

from tblinsa;

ex)직원 남자 몇명? 여자 몇명?

select

    count(*) as "전체 직원수",

    count(decode(substr(ssn,8, 1),'1','1')) as "남자직원수",

    count(decode(substr(ssn,8, 1),'2','1')) as "여자직원수"

from tblinsa;

10. nvl, nvl2

- null 함수, null value

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

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

ex)

select name,nvl(population,0) from tblcountry;

select name,nvl(population,100)+10 from tblcountry;

select name,nvl(population,'인구수 미기재') from tblcountry; -- 대체값은 원본값과 같은 자료형으로 써야한다.

select name,nvl(tel,'010-1234-5678') from tblinsa;

select name,nvl(tel,'연락처 없음') from tblinsa;