06_SQL함수(1) 단일행함수: 문자함수

 

단일행함수와 그룹함수

단일행 함수(Single-Row Function)는 테이블의 n개의 레코드에 각각 적용되어 'n개의 결과값' 산출
그룹함수(Aggregate Function) 는 여러 개(레코드) 를 동시에 입력받아 ‘1개의 결과값’ 산출

 

단일행함수와 그룹함수 비교

 

단일행함수의 종류

 

단일행함수의 종류

     

1) 문자함수

 

(1) INITCAP(문자열 또는 컬럼명)

첫글자만 대문자로 출력하고 나머지는 소문자로 출력하는 함수

 

Ex) SELECT INITCAP('pretty girl') FROM DUAL;

 = 'pretty girl' 문자열에 대해 첫글자만 대문자로 출력하라

*dual 은 오라클에서 제공하는 단일 행, 단컬럼 더미테이블

INITCAP()   

 

 

(2) LOWER(문자열 또는 컬럼명)

입력값을 소문자로 변경해서 출력하는 함수

 

(3) UPPER(문자열 또는 컬럼명)

입력값을 대문자로 변경해서 출력하는 함수

 

Ex) SELECT NAME 이름, ID, LOWER(ID) "소문자", UPPER(ID) "대문자" FROM T_STUDENT;

=) t_student 테이블에서 학생들의 id를 이름과 함께 소문자, 대문자로 출력하라

 

LOWER()과 UPPER()

 

 

(4) LENGTH(문자열 또는 컬럼명) 

문자열의 길이, 즉 문자의 개수를 리턴

 

(5) LENGTHB(문자열 또는 컬럼명)

문자열의 길이에 따른 바이트 수 리턴

*문자열 바이트 크기에 있어 인코딩이 만약 utf-8로 되어 있으면 한글은 ‘한 글자’당 ‘3byte’로 계산!

 

Ex) SELECT NAME, LENGTH(NAME) 글자수, LENGTHB(NAME) 바이트 FROM T_STUDENT WHERE DEPTNO1 = 201; 

= t_student 테이블에서 1전공이 201번인 학생들의 이름과 이름의 글자수, 이름의 바이트 수를 출력하라

LENGTH()와 LENGTHB()

 

 

 

(6) CONCAT('문자열1','문자열2',....)

|| 연산자와 동일한 기능을 수행하는 문자열을 연결하는 함수

 

Ex) SELECT CONCAT(NAME, POSITION) 교수님명단 FROM T_PROFESSOR;

t_professor 테이블에서 교수들의 이름(name)과 직급(position)을 연결해서 하나의 컬럼으로 출력하라

 

CONCAT()

 

 

 

(7) SUBSTR(문자열 또는 컬럼명, 시작위치, 추출할 글자수)

문자열에서 특정 문자 또는 문자열을 추출하는 함수 

 

Ex) SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; 

= 'ABCDE'라는 문자열에서 3번째 글자부터 2개의 글자를 출력하라   -- 'CD'가 출력됨
Ex) SELECT SUBSTR('ABCDE',  -2, 2) FROM DUAL; 

= 'ABCDE'라는 문자열에서 -2번째 글자부터 2개의 글자를 출력하라  -- 'CD' 출력 >> 음수인덱싱 지원(오른쪽에서 왼쪽방향으로 읽기)

*주의 DB에서 값의 위치 인덱스는 1부터 시작된다. 0이 아님에 주의!

Ex) SELECT name, SUBSTR(JUMIN, 1, 6) 생년월일 FROM T_STUDENT WHERE SUBSTR(JUMIN, 3, 2) = 08; 

= t_student 테이블에서 jumin 칼럼을 사용해서  태어난 달이 8월인 사람의 이름과 생년월일을 출력하라

 

SUBSTR()

(8) SUBSTRB(문자열 또는 컬럼명, 시작위치, 바이트를 추출할 글자수)

SUBSTR() 함수와 문법은 동일하지만, 차이점은 추출할 문자수가 아니라 추출할 바이트수 리턴

 

Ex) SELECT SUBSTRB(name, 1, 3) FROM t_student WHERE deptno1 = 101;

= t_student 테이블에서 학과가 101인 학생들의 이름을 출력하되 성만 출력하라

SUBSTRB()

 

 

 

 

(9) INSTR('문자열', '찾는 문자열', 검색시작위치, 몇번째값 )

주어진 문자열이나 칼럼에서 특정 글자의 위치를 찾아주는 함수

 

Ex) SELECT INSTR('A*B*C*', '*', 1, 1) FROM dual;   -- 2 출력

 = 'A*B*C*'라는 문자열에서 '*'문자를 검색하되, 1번 인덱스 문자(A)부터 검사를 시작해서

   첫번째로 '*'가 발견된 위치에 대해 인덱스 번호를 리턴하라  -- 2 출력

Ex) SELECT INSTR('A*B*C*', '*', 1, 2) FROM dual;  

 = 'A*B*C*'라는 문자열에서 '*'문자를 검색하되, 1번 인덱스 문자(A)부터 검사를 시작해서

   두번째로 '*'가 발견된 위치에 대해 인덱스 번호를 리턴하라  -- 4 출력

Ex) SELECT INSTR('A*B*C*', '*', -4, 1) FROM dual;  

 = 'A*B*C*'라는 문자열에서 '*'문자를 검색하되, -4번 인덱스 문자(B)부터 검사를 시작해서

   첫번째로 '*'가 발견된 위치에 대해 인덱스 번호를 리턴하라  -- 2 출력

 

<SUBSTR과 결합된 예제>

Ex) SELECT NAME, tel, INSTR(TEL, ')', 1, 1) AS 위치 FROM T_STUDENT WHERE DEPTNO1 = 101; 

= t_student 테이블 : tel 칼럼을 사용하여 101번 학과(deptno1) 의 학생의 이름과 전화번호, ‘)’ 가 나오는 위치 출력하라  

Ex) SELECT NAME, TEL, SUBSTR(TEL, 1, INSTR(TEL, ')', 1, 1) - 1) 지역번호 FROM T_STUDENT WHERE DEPTNO1=101;

= t_student 테이블 : 1전공이 101 인 학생의 이름과 전화번호, 지역번호를 출력하되 전화번호는 지역번호만 출력하라  

 

 

 

 

 

(10) LPAD('문자열', 문자열표현길이, '채울문자')

문자열을 입력받은 길이(크기)만큼 출력하고 문자열 왼쪽의 빈자리가 있으면 '채울문자'로 출력

 

Ex) SELECT ID, LPAD(ID, 10, '$') LPAD FROM T_STUDENT WHERE DEPTNO1=101;

= t_student 테이블에서 1전공이 101번인 학생들의 id를 총 10자리로 출력하고 왼쪽 빈자리는 ‘$’ 기호로 채워라

LPAD()

 

(11) RPAD('문자열', 자릿수, '채울문자')

문자열을 입력받은 길이(크기)만큼 출력하고 문자열 오른쪽의 빈자리가 있으면 '채울문자'로 출력

 

Ex) SELECT RPAD(ID, 12, '*') RPAD연습 FROM T_STUDENT;

= Student 테이블에서 다음과 같이 id를 12자리로 출력하되 오른쪽 빈자리에는 ‘*’ 로 채워라

RPAD()

 

 

 

 

(13) LTRIM('문자열', '제거할문자')

(14) RTRIM('문자열', '제거할문자')

문자열 좌(우)에서 문자 (반복) 제거

 

Ex) SELECT LTRIM(DNAME, '영') FROM T_DEPT2;

t_dept2 테이블에서 부서명(dname) 을 출력하되 왼쪽에 ‘영’ 이란 글자를 모두 제거하고 출력하라

LTRIM()

 

 

 

 

(15) REPLACE('문자열','찾을문자','바꿀문자')

문자열에서 특정 문자(열)를 찾아서 원하는 문자(열)로 변경

 

Ex) SELECT REPLACE('슈퍼맨 슈퍼걸', '슈퍼', '파워') REPLACE예제 FROM dual;

REPLACE()

 

Ex) SELECT REPLACE(NAME, SUBSTR(NAME, 1, 1), '#') FROM T_STUDENT;

= t_student 테이블에서 학생들의 이름을 출력하되 성 부분은 ‘#’  으로 표시되게 출력하라

REPLACE() 1

 

Ex) SELECT NAME, REPLACE(JUMIN, SUBSTR(JUMIN, 7, 7), '*******') 주민번호 FROM T_STUDENT;

= t_student 테이블에서 학생들의 이름과 주민등록번호를 출력하되, 주민번호의 뒤 7자리는 ‘*’ 로 표시하라

REPLACE() 2

 

Ex) SELECT NAME, REPLACE(TEL, SUBSTR(TEL, INSTR(TEL, ')', 1)+1, 3), '###') FROM T_STUDENT;

= 전화번호에서 국번 부분만 ‘#’ 처리하여 출력하세요.  

REPLACE() 3

 

 

문자함수의 종류 정리

 

문자함수의 종류