[Oracle | 오라클] 단일행 함수 - 1. 문자함수

※ 이 글은 오지영 강사님의 <업무에 바로 쓰는 SQL 활용실습>의 강의내용을 바탕으로 작성하였습니다.

1. 대소문자 변환함수

LOWER(char) 문자열을 소문자로 변환

UPPER(char) 문자열을 대문자로 변환

INITCAP(char) 주어진 문자열의 첫 번째 문자를 대문자로 나머지 문자는 소문자로 변환

SELECT e_player_name, UPPER(e_player_name), LOWER(e_player_name), INITCAP(e_player_name)

FROM player_t

WHERE player_name LIKE '가%';

 

2. 문자열의 일부분만 추출하는 함수

SUBSTR(대상, 추출시작위치, 개수)

 

SELECT e_player_name, substr(e_player_name,3,4)

FROM PLAYER_T

WHERE player_name LIKE '가%';

SELECT e_player_name, substr(e_player_name,3)

FROM PLAYER_T

WHERE player_name LIKE '가%';

--e_player_name의 3번째 문자부터 끝까지 모두 가져옴

 

*substr/substrb와 length/lengthb

SELECT e_player_name, substr(e_player_name,1,4), substrb(e_player_name,1,4)

FROM PLAYER_T

WHERE player_name='가이모토';

--한글인 경우 substr/substrb 함수 적용 결과값이 다름

 

SELECT e_player_name, length(player_name), lengthb(player_name)

FROM PLAYER_T

WHERE player_name='가이모토';

 

3. 특정 자릿수만큼 채워서 표시할 때

LPAD(대상, 확보할 크기, 남은공간 채울 문자), 

RPAD(대상, 확보할 크기, 남은공간 채울 문자)

SELECT RPAD(sal, 8, '0') 

FROM emp;

SELECT LPAD(sal, 8, '0') 

FROM emp;

 

4. 왼쪽끝/ 오른쪽 끝에 나오는 특정문자(또는 공백) 제거

LTRIM(대상, 제거할 문자)

RTRIM(대상, 제거할 문자)

*제거할 문자가 연속된다면 모두 제거된다.

SELECT LTRIM(ename, 'A') FROM emp;

SELECT RTRIM(ename, 'T') FROM emp;

*양쪽 끝에 나오는 지정된 문자 제거 시

SELECT ename, TRIM('T' from ename) 

FROM emp;

 

5. 특정 문자열을 다른 문자열로 변경할 때

replace(대상, 찾을 문자열, 변경할 문자열)

REPLACE(ename, ‘SC’, ‘*?’) from emp;

 

*비교> select ename, TRANSLATE(ename, ‘SC’, ‘*?’);

TRANSLATE(ename, ‘SC’, ‘*?’) from emp;

※TRANSLATE는 각각 한글자씩만 대응가능하다.

 

6. 지정한 문자가 어느 위치에 나오는지 알려주는 함수

INSTR(대상, 찾을 문자열, 검색시작위치, 횟수)

SELECT ename, INSTR(ename,'A',1,1)

FROM emp;

    SMITH   --> 0

    ADAMS   --> 1

    WARD    --> 2

 

SELECT ename, INSTR(ename,'A',1,2)

FROM emp;

    SMITH   --> 0

    ADAMS   --> 3

    WARD    --> 0

 

player_t 선수 중 이름 1글자, 3글자 아닌 선수들의 이름을 아래 형태로 나오도록 조회하기

(이름첫글자--두번째글자부터 남은글자)

SELECT SUBSTR(player_name,1,1)||'--'||SUBSTR(player_name,2)

FROM player_t

WHERE length(player_name) NOT IN (1,3);

 

7. 특정 문자의 ASCII 코드 값을 반환하는 함수

SELECT ASCII(char) 

FROM dual;

 

SELECT ASCII('a') 소문자, ASCII('A') 대문자 FROM dual;

 

 

 

연습문제

 

1. player_t 선수중에 e_player_name에 E가 4번이상 나오는 선수들의 이름, 영문이름 조회하기

 

        SELECT player_name, e_player_name

        FROM PLAYER_T

        WHERE e_player_name LIKE '%E%E%E%E%';

 

        SELECT player_name, e_player_name

        FROM PLAYER_T

        WHERE INSTR(e_player_name, 'E', 1, 4) > 0;

 

 

2. player_t 선수중 이름이 2글자인 선수들의 한글이름, 영문이름,영문이름 중 첫 번째 공백 앞글자만 나오도록 조회하기

ex) KIM, HO  인 경우==>  KIM,까지

Gabriel Popescu 인 경우==> Gabriel까지만 나오도록

 

         SELECT player_name, e_player_name, SUBSTR(e_player_name, 1, instr(e_player_name, ' ')-1)

         FROM PLAYER_T

         WHERE e_player_name LIKE '% %';

 

3. team_t테이블 이용해서 team_name, tel, tel정보를 한글로 표시해서 조회하기

        ex) 전화번호가 202-9998인경우==> 이영이-구구구팔 

 

        SELECT team_name, tel, TRANSLATE(tel, '0123456789', '영일이삼사오육칠팔구') 한글전화전호

        FROM TEAM_T;

    

4. 이메일 주소(ongion@naver.com)의 @ 앞 부분만 조회하기

     

 SELECT SUBSTR('ongion@naver.com',1,INSTR('ongion@naver.com','@')-1) 

 FROM dual;

 

5. 이메일 주소(ongion@naver.com)의 @ 뒷 부분만 조회하기

         

 SELECT SUBSTR('ongion@naver.com',INSTR('ongion@naver.com','@')+1) 

 FROM dual;