[SQL] 2. 단일행함수(문자함수)

단일행 함수

문자 함수

INITCAP(컬럼명 or '문자열') : 영어에서 첫 글자만 대문자로 출력(나머지는 소문자)

LOWER(컬럼명 or '문자열') / UPPER() : 입력되는 값을 전부 소문자/대문자로 출력

LENGTH(컬럼명 or '문자열') / LENGTHB() : 입력된 문자열의 길이/바이트수 를 계산

CONCAT('문자열1', '문자열2') = || 연산자와 동일

SUBSTR(컬럼명 or '문자열', 시작위치, 자릿수) : 주어진 문자열에서 특정길이의 문자만 추출/ SUBSTRB()의 자릿수에는 추출할 바이트수를 지정

• 시작위치 숫자를 -(마이너스)로 지정할 경우 뒤에서부터(오른쪽) 세어 시작위치를 지정/ 자릿수는 left -> right 방향으로 동일
• ex) Student 테이블에서 jumin 컬럼을 사용해서 1 전공이 101번인 학생들의 이름과 태어난 월일 , 생일 하루 전 날짜를 출력하시오

  SQL> SELECT name, SUBSTR(jumin,3,4) "Birthday",
  2               SUBSTR(jumin,3,4)-1 "Birthday - 1"
  3  FROM student
  4  WHERE deptno1 = 101;
  
  NAME                 Birthday Birthday - 1
  -------------------- -------- ------------
  James Seo            1023             1022
  Billy Crystal        0123              122
  Richard Dreyfus      1129             1128
  Danny Devito         0819              818 

INSTR(컬럼명 or '문자열', 찾는글자, 시작위치(기준), 몇번째(default:1)) : 특정 글자의 위치를 추출

• 시작 위치를 -(마이너스)로 지정할 경우 left <- right(오른쪽에서 왼쪽) 으로 검색해 시작위치지정
• 시작위치와 찾으려는 위치 값은 생략가능하며 생략할 경우자동으로 1로 설정
• ex) 문자열 A-B-C-D에서 오른쪽에서 6번째 문자를 기준으로 2번째'-'가 나오는 위치의 값을 출력하시오.

  SQL> SELECT 'A-B-C-D' "ABCD", INSTR('A-B-C-D','-', -6, 2) "INSTR"
  2  FROM dual;
  
  ABCD         INSTR
  ----------- ---------
  A-B-C-D             0  

• ex2) Student 테이블의 tel 컬럼을 사용하여 1 전공번호(deptno1)가 201번인 학생의 이름과 전화번호, ‘)‘ 가 나오는 위치를 출력하시오.

  SQL> SELECT name, tel, INSTR(tel, ')') "INSTR()"
   2  FROM student
  3  WHERE deptno1 = 201;
  
  NAME                 TEL                INSTR()
  -------------------- --------------- ----------
  
  Demi Moore           02)6255-9875             3
  Macaulay Culkin      02)312-9838              3
  Wesley Snipes        053)736-4981             4    

LPAD(컬럼 or '문자열', 전체자리수, '채울문자') / RPAD() : 원래 문자열을 제외한 나머지 자리(왼쪽/오른쪽)에 문자를 채움.

• ex) student 테이블에서 1 전공이 201 번인 학과 학생들의 id 를 총 10자리로 출력하되 왼쪽 빈 자리는 ‘*’ 기호로 채우세요

  SQL> SELECT name, id, LPAD(id,10,'*') "LPAD()"
  2  FROM student
  3  WHERE deptno1 = 201;
  
  NAME                 ID         LPAD()
  -------------------- ---------- ------------------- 
  Demi Moore           Moore      *****Moore
  Macaulay Culkin      Culkin     ****Culkin
  Wesley Snipes        Snipes     ****Snipes 

LTRIM(컬럼 or '문자열', '제거할문자') / RTRIM() : 해당문자를 제거

• ex) emp 테이블의 ename에서 마지막 글자가 'R'이 있을경우 제거함.

  SQL> SELECT ename, RTRIM(ename, 'R') "RTRIM"
  2  FROM emp
  3  WHERE deptno = 10;
  
  ENAME      RTRIM
  ------- --------
  CLARK      CLARK
  KING       KING
  MILLER     MILLE 

REPLACE(컬럼 or '문자열', '문자1', '문자2') : 주어진 문자열에서 문자1을 문자2로 바꿔 출력

• ex) emp 테이블에서 이름의 첫 두글자를 *로 표시하시오.

  SQL> SELECT ename, REPLACE(ename, SUBSTR(ename, 1,2), '**') "REPLACE"
  2  FROM emp
  3  WHERE deptno = 10;
  
  ENAME      REPLACE
  ---------- --------------------
  CLARK      **ARK
  KING       **NG
  MILLER     **LLER 

QUIZ1

Student 테이블을 참조해서 아래 화면과 같이 1 전공이(deptno1 컬럼) 201번인 학생의 이름과 전화번호와 지역번호를 출력하세요. 단 지역번호는 숫자만 나와야함. 

SQL> SELECT name, tel, SUBSTR(tel,1,INSTR(tel,')')-1) "Area Code"
  2  FROM student
  3  WHERE deptno1 = 201;

QUIZ2

아래 화면과 같이 emp 테이블에서 deptno 가 10번인 사원들의 이름을 총 9자리로 출력하되 오른쪽 빈자리에는 해당 자리 수에 해당되는 숫자가 출력되도록 하시오 

SQL> SELECT RPAD(ename, 9, SUBSTR('123456789',length(ename)+1,9)) "RPAD"
  2  FROM emp
  3  WHERE deptno = 10; 

QUIZ3

Student 테이블에서 아래와 같이 deptno1 이 101 번인 학과 학생들의 이름과 전화번호와 전화번호에서 지역번호와 국번을 제외한 나머지 번호를 * 로 표시해서 출력하시오.

SQL> SELECT name, tel, REPLACE(tel, SUBSTR(tel,INSTR(tel,'-')+1), '****') "REPLACE"
  2  FROM student
  3  WHERE deptno1 = 101; 

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저