[Oracle] 문자열 함수

//함수

1. 문자열 함수

2. 숫자 함수

3. 날짜 함수

4. 변환 함수

5. NULL 관련 함수

6. 집계 함수

---

//문자열 함수

---

// SUBSTR(문자열, 시작 위치, 길이) : 문자열 추출
    ~ SUBSTRB(문자열, 시작 위치, 길이) : 바이트 단위로

SELECT SUBSTR('HELLO' 1, 3) FROM DUAL; --'HEL'

SELECT SUBSTR('HELLO' 3) FROM DUAL; --'LLO'

SELECT SUBSTR('HELLO', 3) FROM DUAL

---

- 성 빼고 이름만 조회

SELECT SUBSTR(NAME, 2) FROM MEMBER;

---

- 모든 학생의 이름과 출생 월만을 조회

SELECT NAME, SUBSTR(BIRTHDAY, 6, 2) BIRTHDAY_MONTH FROM MEMBER;

---

- 회원 중에서 출생 월이 5,6,7월인 회원의 모든 정보 조회

SELECT * FROM MEMBER WHERE SUBSTR(BIRTHDAY, 6, 2) BETWEEN '05' AND '07';
SELECT * FROM MEMBER WHERE SUBSTR(BIRTHDAY, 6, 2) IN ('05', '06', '07');

---

- 전화번호를 등록하지 않은 회원 중에서 생년 월이 5,6,7월인 회원의 모든 정보 조회

SELECT * FROM MEMBER WHERE PHONE IS NULL AND SUBSTR(BIRTHDAY, 6, 2) IN ('05', '06', '07');

---

select 
    title,
    substr(title, 3),
    substr(title, 3, 4)
from tblTodo;

---

select
    name,
    substr(name, 1, 1) as "성",
    substr(name, 2) as "이름",
    ssn,
    substr(ssn, 1, 2) as "생년",
    substr(ssn, 3, 2) as "생월",
    substr(ssn, 5, 2) as "생일",
    substr(ssn, 8, 1) as "성별"
from tblInsa;

---

select
count(case
        when substr(name, 1, 1) = '김' then 1
    end) as "김",
    count(case
        when substr(name, 1, 1) = '이' then 1
    end) as "이",
    count(case
        when substr(name, 1, 1) = '박' then 1
    end) as "박",
    count(case
        when substr(name, 1, 1) = '최' then 1
    end) as "최",
    count(case
        when substr(name, 1, 1) = '정' then 1
    end) as "정",
    count(case
        when substr(name, 1, 1) not in ('김', '이', '박', '최', '정') then 1
    end) as "나머지"
from tblInsa;

---

// CONCAT(문자열1, 문자열2) : 문자열 덧셈(연결)
    ※ 성능면에서 보면 문자열 연산 || 이 더 빠름.  > 더 자주 사용

SELECT CONCAT('홍', '길동') FROM DUAL; --'홍길동'

---

// TRIM(문자열) : 문자열에 있는 공백 제거(왼쪽 오른쪽 모두)
    ~ LTRIM(문자열) : 왼쪽 공백 제거
    ~ RRIM(문자열) : 오른쪽 공백 제거

SELECT TRIM('	HELLO	') FROM DUAL; --'HELLO'

SELECT LTRIM('	HELLO	') FROM DUAL; --'HELLO	'

SELECT RTRIM('	HELLO	') FROM DUAL; --	'HELLO'

---

select
    '   하나  둘   셋   ',
    trim('   하나  둘   셋   '),
    ltrim('   하나  둘   셋   '),
    rtrim('   하나  둘   셋   ')
from dual;

---

// LOWER(문자열) : 문자열의 모든 문자를 소문자로 변경
// UPPER(문자열) : 문자열의 모든 문자를 대문자로 변경
// INITCAP(문자열) : 첫글자만 대문자, 나머진 소문자로 변경 (카멜 표기법)

SELECT LOWER('SorREl') FROM DUAL; --'sorrel'

SELECT LOWER('SorREl') FROM DUAL; --'SORREL'

SELECT INITCAP('SorREl') FROM DUAL; --'Sorrel'

---

- 소대문자를 가리지 않고 아이디 조회

SELECT * FROM MEMBER WHERE LOWER(ID) = 'youl';

SELECT * FROM MEMBER WHERE UPPER(ID) = 'YOUL';

---

select
    'abc', initcap('abc'), initcap('aBC')
from dual;

---

// REPLACE(문자열, 찾는 문자열, 대치할 문자열) : 문자열에서 특정 부분 문자열을 원하는 문자열로 변경

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

// DECODE(문자열, 찾는 문자열, 대치할 문자열, [, 찾는 문자열, 대치할 문자열] X N) 

- 문자열 치환
- replace와 비슷하지만 상황에 따라 훨씬 편함
- varchar2 decode(컬럼, 찾을 문자열, 바꿀 문자열 [, 찾을 문자열, 바꿀 문자열] x N)
- 문자열 조작 > case의 간단한 버전
- 문자열을 못 찾으면 null 반환

// TRANSLATE(문자열, 찾는 문자열, 대치할 문자열) : 문자열에서 특정 부분 문자를 원하는 문자로 변경

SELECT REPLACE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; --'WHERE YOU ARE'

SELECT TRANSLATE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; --'YHORO YO ARO'

---

- REPLACE : 회원의 이름과 생년월일 조회(단, 생년월일은 숫자만)

SELECT NAME, REPLACE(BIRTHDAY, '-', '') BIRTHDAY FROM MEMBER;

---

- decode

select
    gender,
    case
        when gender = 'm' then '남자'
        when gender = 'f' then '여자'
    end as g1,
    
    replace(replace(gender, 'm', '남자'), 'f', '여자') as g2,
    
    decode(gender, 'm', '남자','f', '여자') as g3
    
from tblComedian

---

select
    count(decode(gender, 'm', 1)) as "남자",
    count(decode(gender, 'f', 1)) as "여자"
from tblComedian;

---

- LPAD(문자열, 수, 채우고 싶은 문자) : 문자열 패딩 (왼쪽 채우기)
- RPAD(문자열, 수, 채우고 싶은 문자) : 문자열 패딩 (오른쪽 채우기)

    ※ 문자가 수만큼 이미 채워져있으면 그대로 반환, 모자라면 지정 문자로 채움
    ※ 한글의 경우 byte가 더 크기 때문에 원하는 글자수 8 2를 해서 입력해야 함.

SELECT LPAD('HELLO', 5) FROM DUAL; --HELLO

SELECT LPAD('HELLO', 5, '0') FROM DUAL; --HELLO

SELECT LPAD('HELLO', 10, '0') FROM DUAL; --00000HELLO

SELECT RPAD('HELLO', 10, '0') FROM DUAL; --HELLO00000

---

- 회원 이름 조회(이름의 길이가 3자가 안 되는 경우 '_'로 채우시오.)

SELECT RPAD(NAME, 6, '_') NAME FROM MEMBER;

---

select 
    'a',
    lpad('a', 5, 'b'),
    '1',
    lpad('1', 3, '0'),
    lpad('12', 3, '0'),
    lpad('123', 3, '0'),
    lpad('1234', 3, '0'),
    rpad('1', 3, '0')
from dual;

---

// INSTR(문자열, 검색문자열, 시작 위치,  순서)

- 검색함수(indexOf)
- 검색어의 위치를 반환
- number instr(컬럼, 검색어)
- number instr(컬럼, 검색어, 시작 위치)
- number instr(컬럼, 검색어, 시작 위치, -1) > lastIndexOf
- 못 찾으면 0 반환

SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO') FROM DUAL;  --13

SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO', 15) FROM DUAL; --24

SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO', 1, 2) FROM DUAL; --24

---

select
    '안녕하세요. 홍길동님',
    instr('안녕하세요. 홍길동님', '홍길동') as r1,
    instr('안녕하세요. 홍길동님', '아무개') as r2,
    instr('안녕하세요. 홍길동님. 홍길동님!', '홍길동') as r3,
    instr('안녕하세요. 홍길동님. 홍길동님', '홍길동', 11) as r4,
    instr('안녕하세요. 홍길동님. 홍길동님', '홍길동'
            , instr('안녕하세요. 홍길동님. 홍길동님!', '홍길동') + length('홍길동')) as r5,
    instr('안녕하세요. 홍길동님. 홍길동님!', '홍길동', -1) as r6
from dual;

---

- 회원의 전화번호에서 두 번째 대시(-) 문자가 존재하는 위치 조회

SELECT INSTR(PHONE, '-', 1, 2) PHONE FROM MEMBER;

---

- 회원의 전화번호에서 첫 번째 대시(-)와 두 번째 대시(-) 문자 사이의 간격 조회

SELECT INSTR(PHONE, '-', 1, 2) - INSTR (PHONE, '-', 1, 1) - 1 PHONE FROM MEMBER;

---

// LENGTH(문자열) : 문자열 길이 반환

SELECT LENGTH('WHERE WE ARE') FROM DUAL; --12

---

- 회원의 전화번호에서 '-'를 없앤 전화번호의 길이 조회

SELECT LENGTH(REPLACE(PHONE, '-', ''))PHONE FROM MEMBER;

---

select name, length(name) from tblCountry order by length(name) desc;

---

- 게시판 제목이 길면 잘라서 말줄임표 표시(..)

select 
    title,
    case
        when length(title) >= 8 then substr(title, 1, 8) || '..'
        else title
    end
from tblTodo;

---

// ASCII(문자열) : 문자 -> 문자코드 값
// CHR(문자코드값) : 문자코드 값 -> 문자

SELECT ASCII('A') FROM DUAL; --65

SELECT CHR(122) FROM DUAL; --'z'