SQL 단일행 함수 - 문자함수

MIN.DI·2021년 5월 18일
0

SQL

목록 보기
9/17

1. 대소문자 변환 함수

LOWER : 문자열을 소문자로 변환
UPPER : 문자열을 대문자로 변환
INITCAP : 첫 문자만 대문자로 변환하고 나머지는 소문자로 변환

2. 문자 연산함수

CONCAT : 두개의 문자열을 연결

>> CONCAT(문자열1, 문자열2)
  CONCAT('문','시현') => 문시현

SUBSTR : 문자열 내에 지정된 위치의 문자열을 반환 **많이쓰임

>> SUBSTRING (문자열, [시작위치], 출력문자의 개수)
  SUBSTR('ORACLE', 1, 2) => OR      (자바는 0부터 시작하지만 오라클은 1부터 시작)

LENGTH : 문자열의 길이나 BYTE 를 반환

>> LENGTH(문자열), LENGTHB(문자열)
  LENGTH('오라클') => 3
  LENGTHB('오라클) => 6 (바이트 수)

INSTR : 지정된 문자의 위치를 리턴

>> INSTR(문자열, 검색문자, [시작위치, [횟수]])
  INSTR('ORACLE', 'A') => 3

TRIM : 접두어나 접미어 잘라냄

>> TRIM([LEADING | TRAILING | BOTH] 제외문자 FROM 문자열)
  제외문자를 지정하지 않으면 공백문자를 잘라냄.
  TRIM('O' FROM 'ORACLE') => RACLE
  
  select ltrim('mississippi','mis')
  from dual;  
  --m, i, s를 각각 지우고, p(지우고자 하는 문자가 아닌 문자)를 만나면 리턴
    ('mis'를 패턴처럼 하나의 문자열로 인식하는것이 아니라, 각각의 문자로 인식)

  select ltrim('Mississippi','mis')
  from dual; 
  --첫 시작이 대문자M으로, m, i, s를 만나지 못했으므로 아무것도 지우지 않고 리턴

LPAD, RPAD : 지정된 문자열의 길이만큼 빈 부분에 문자를 채움

>> LPAD : 오른쪽정렬
   RPAD : 왼쪽정렬
>> LPAD(문자열, 출력폭, 채움문자)
  LPAD('2000', 10, '*') => '******2000'
  RPAD('2000', 10, ' ') => '2000      '
  = RPAD('2000',10) 
  (남은 부분을 공백으로 채울 경우 채움문자 생략 가능)
  
  출력폭은 임의의 크기를 입력하되, 해당 컬럼의 데이터타입의 크기보다는 큰 크기로 지정해야한다.
  (예를들어 해당 컬럼 데이터타입이 VARCHAR2(10 BYTE)라면 10 BYTE보다는 큰 크기로 지정해야 
  데이터가 잘리지 않고 조회된다)
  
  

3. 문자 치환 함수

TRANSLATE : '문자'단위 치환된 값 리턴.

>> TRANSLATE(문자열, 검색문자, 치환문자)
  TRANSLATE('ORACLE', 'A', '#') => OR#CLE
  
  SELECT  ename, translate(ename, 'SC','*?') 변경결과2 
  FROM emp
  WHERE deptno = 20;
 --'S'='*', 'C'='?' 로 맞교환. 각각의 문자로 인식.        
  만약 대응되는 값이 없을경우 그냥 삭제되고, 공백을 주면 공백으로 대체.

REPLACE : '문자열' 단위 치환된 값을 리턴

문자열을 하나의 패턴처럼 인식한다.

>> REPLACE(문자열, 검색문자열, 치환문자열)
  REPLACE('ORACLE', 'OR', '##;) => ##ACLE
  

EXERCISE 1

SELECT DNAME, LOC ERP_부서_지역
FROM DEPT
WHERE LOWER(DNAME) = 'erp';

EXERCISE 2

부서명과 위치를 하나의 컬럼으로 검색

SELECT CONCAT(DNAME, ' ' || LOC) CONCAT FROM DEPT;

EXERCISE 3

부서명과 문자열의 길이를 출력

SELECT DNAME, LENGTH(DNAME), LENGTHB(DNAME) FROM DEPT;

EXERCISE 4

SUBSTR 함수를 이용해서 컬럼에 일부 내용만을 검색

SELECT ENAME
      ,SUBSTR (ENAME, 2)
      ,SUBSTR (ENAME, -2)
      ,SUBSTR (ENAME, 1, 2)
      ,SUBSTR (ENAME, -2, 2)
FROM EMP;

EXERCISE 5

다양한 방법으로 'A'가 나오는 위치 출력

SELECT INSTR('DATABASE', 'A')   -- D'A'TABASE   (처음부터 검색)
      ,INSTR('DATABASE', 'A', 3)    --DAT'A'BASE (세번째부터 검색)
      ,INSTR('DATABASE', 'A', 1, 3) --DATAB'A'SE (첫번째부터 검색, 세번째 A를 찾기)
FROM DUAL;      

EXERCISE 6

TRIM 함수를 이용하여 다양한 방법으로 문자열 검색

SELECT ' 남기남 ' , TRIM (' 남기남 '), LTRIM(' 남기남 '), RTRIM(' 남기남 ') FROM DUAL;

EXERCISE 7

이름과 급여를 각각 10컬럼으로 검색

SELECT RPAD(ENAME, 10, '*'), LPAD(SAL, 10, '*') FROM EMP;   --BYTE단위

EXERCISE 8

부서명의 마지막 글자를 제외하고 검색

SELECT DNAME, SUBSTR(DNAME, 1, LENGTH(DNAME)-1) DNAME 
            , SUBSTR(DNAME, LENGTH(dNAME), 1) DNAME2
FROM DEPT;    --글자수 단위

EXERCISE 9

형식이 비슷한 TRANSLATE 와 REPLACE 를 동일한 치환을 통해 비교

SELECT TRANSLATE('WORLD OF WARCRAFT', 'WO', '--') TR,
       REPLACE('WORLD OF WARCRAFT', 'WO', '--') RE
FROM DUAL;

1. 이름이 두글자인 학생의 이름 검색

SELECT * FROM STUDENT WHERE LENGTH(SNAME) = 2;  --조건절 컬럼에는 가급적 함수를 씌우지 않는것이 좋다
                                                --(함수를 씌우면 INDEX조회가 되지 않아 FULL SCAN 함 -> 처리속도 느려짐)

2.'강'씨 성을 가진 학생의 이름 검색

SELECT * FROM STUDENT WHERE SUBSTR(SNAME, 1, 1)='강';

3.교수의 직위를 한글자로 검색 (정교수 -> 정)

SELECT PNO, PNAME, SUBSTR(ORDERS, 1, 1) FROM PROFESSOR;

4.일반 과목을 기초 과목으로 변경해서 모든 과목을 검색(일반화학 -> 기초화학)

SELECT CNO, CNAME, REPLACE(CNAME, '일반', '기초') FROM COURSE
WHERE CNAME LIKE '%일반%';

5. 입력실수로 STUDENT 테이블의 SNAME컬럼에 데이터가 입력될 때 문자열 마지막에 공백이 추가되었다면

검색할 때 이를 제외하고 검색하는 SELECT문 작성
SELECT TRIM(SNAME) FROM STUDENT;

6. 직원의 연봉을 10자리로 검색 (단 공백은 임의의 채움 문자로 채워넣음)

SELECT LPAD(SAL*12+NVL(COMM, 0), 10, '*') FROM EMP;

7. 학생의 이름을 검색. 단 '심'씨인 학생은 '사마'씨로 바꾸어 검색

SELECT SNAME, REPLACE(SUBSTR(SNAME, 1, 1), '심', '사마') || SUBSTR(SNAME, 2) FROM STUDENT;
profile
내가 보려고 쓰는 블로그

0개의 댓글