[함수]_문자

오라클 함수

-내장함수 : 단일행 함수, 다중행 함수

-사용자 정의함수 : PL/SQL(여기서 다루진 않음)

 

 

0. 문자열 데이터를 연결하는 || 연산자

SELECT EMPNO || ENAME,           --결과 : EMPNOENAME
       EMPNO || ' : ' || ENAME   --결과 : EMPNO : ENAME
FROM EMP;

1. 내장함수

-단일행 함수

-다중행 함수

 

(1) 대소문자 변환 : UPPER, LOWER, INITCAP

--UPPER : 괄호 안 문자 데이터를 모두 대문자 변환 후 반환
SELECT *
  FROM EMP
 WHERE UPPER(ENAME) = UPPER('scott')

--LOWER : 괄호 안 문자 데이터를 모두 소문자 변환 후 반환
SELECT *
  FROM EMP
 WHERE UPPER(ENAME) LIKE UPPER('%scott%');

--INITCAP : 괄호 안 문자 데이터를 첫 글자 대문자, 나머지 소문자로 변환 후 반환

 

(2) 문자열 길이 : LENGTH, LENGTHB

--LENGTH : 괄호 안의 문자열 길이 반환
SELECT ENAME, LENGTH(ENAME)
  FROM EMP;

--LENGTHB : 괄호 안의 문자열 바이트 수 반환
SELECT ENAME, LENGTHB(ENAME)
  FROM EMP;

(참고)

--한글 문자셋 : UTF8(3BYTE), AL32UTF8(3BYTE)
--자신의 컴퓨터가 어떤 문자셋인지 알려주는 것
SELECT *
  FROM NLS_datbase_parameters
 WHERE PARAMETER LIKE 'NLS_CHARACTERSET';
 
--'한글' 문자열 길이, 바이트 수
SELECT LENGTH('한글'), LENGTHB('한글')
  FROM DUAL;

--결과 : '한글'의 LENGTH는 2 LENGTHB는 6
--DAUL : 더미 테이블. 임시로 값을 알아볼 수 있음.

 

(3) 문자 데이터 가공

SUBSTR, INSTR, REPLACE, CONCAT, TRIM·RTRIM·LTRIM

-SUBSTR

SUBSTR(문자열 데이터, 시작위치, 추출 길이)
STUBSTR(문자열 데이터, 시작위치)

지정한 문자열 데이터의 시작위치에서부터 추출 길이만큼 추출.

시작 위치가 음수면 마지막 위치(맨끝)에서부터 거슬러 올라가 추출(추출방향은 →)

 

문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출.

시작 위치가 음수면 마지막 위치에서부터 거슬러 올라가 끝까지 추출(추출 방향 →)

 

예제)

--JOB(직무)
SELECT JOB,
    SUBSTR(JOB, 1, 2),  --JOB 문자열 데이터에서 시작위치 1, 추출길이 2
    SUBSTR(JOB, 3, 2),  --JOB 문자열 데이터에서 시작위치 3, 추출길이 2
    SUBSTR(JOB, 6)      --JOB 문자열 데이터에서 시작위치 6, 그 뒤로 끝까지 추출
    
    SUBSTR(JOB, -LENGTH(JOB)), --시작위치 -(JOB 문자열 길이), 거기서부터 전부 추출
    SUBSTR(JOB, -LENGTH(JOB), 2) --시작 위치 -(JOB 문자열 길이), 거기서부터 2개 추출
    SUBSTR(JOB, -3) --JOB 문자열 데이터에서 시작위치 -3, 거기서부터 전부 추출
FROM EMP;

→ JOB 문자열 데이터에서

첫 번째 줄은 시작위치가 문자열 맨끝(오른쪽)에서 문자열 맨처음까지 거슬러 올라옴. 음수로. 추출 길이를 지정하지 않아서 전부 추출.

두 번째 줄 역시 맨끝에서부터 맨처음까지 거슬러 올라와 2개 추출(추출방향은 →)

세 번째 줄은 맨끝에서부터 -1, -2, -3까지 거슬러 올라와 -3부터 끝까지 추출. 방향은 → 이며, 추출 길이를 지정하지 않았기 때문에 끝까지 추출.

 

 

-INSTR

문자열 데이터 내 특정 문자 위치 찾기

INSTR(대상 문자열 데이터(필), 위치 찾으려는 문자(필), 시작 위치, 몇 번째 글자 찾을 것인지)

예제)

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
       INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
       INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
FROM DUAL;

→ 대상 문자열 HELLO, ORACLE!

첫 번째 줄은 문자열에서 'L'의 위치를 찾아 별칭 부여한 INSTR_1에 출력,     3

두 번째 줄은 문자열에서 'L'의 위치를 문자열의 5번째에서부터 찾아 INSTR_2에 출력,    12

세 번째 줄은 문자열에서 'L'의 위치를 문자열의 2번째에서부터 찾아 2번째에 나타나는 것을 INSTR_3에 출력.    4

 

 

-REPLACE

특정 문자를 다른 문자로 대체

--왼쪽에 채운다
LPAD(문자열 데이터|열 이름(필)), (데이터 자릿수(필)), (빈 공간 채울 문자)

--오른쪽에 채운다
RPAD(문자열 데이터|열 이름(필)), (데이터 자릿수(필)), (빈 공간 채울 문자)

--LPAD / RPAD
SELECT 'ORACLE',
        LPAD('ORACLE', 10, '#') AS LPAD_1,   --####ORACLE
        RPAD('ORACLE', 10, '*') AS RPAD_1,   --ORACLE****
        LPAD('ORACLE', 10) AS RPAD_1,       --지정하지 않으면 공백 채움
        RPAD('ORACLE', 10) AS RPAD_2
   FROM DUAL;
   
 --주민번호 가림표시 등등
 SELECT RPAD('971225-', 14, '*') AS JMNO,         --971225-*******
        RPAD('010-123-', 13, '*') AS RPAD_PHONE   --010-1234-****
  FROM DUAL;

 

-CONCAT

두 문자를 합침

SELECT CONCAT(EMPNO, ENAME),                --결과 : 7788SCOTT
       CONCAT(EMPNO, CONCAT(' : ', ENAME))  --결과 : 7788 : SCOTT
FROM EMP
WHERE ENAME = 'SCOTT';

 

괄호 먼저 수행.

 

*문자열 데이터를 연결하는 ||연산자

SELECT EMPNO || ENAME,          --EMPNOENAME
       EMPNO || ' : ' || ENAME  --EMPNO : ENAME
  FROM EMP;

 

-TRIM, LTRIM, RTRIM

공백이나 문자를 제거하여 출력

TRIM([삭제 옵션(선)] [삭제할 문자(선)] FROM [원본 문자열 데이터(필)])
LTRIM([원본 문자열 데이터(필)], [삭제할 문자 집합(선)])
RTRIM([원본 문자열 데이터(필)], [삭제할 문자 집합(선)])

예제)

SELECT
  '[' || TRIM(' _ _ORACLE_ _ ') || ']' AS TRIM,  --좌우 공백 삭제
  '[' || TRIM(LEADING FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_LEADING,  --왼쪽 공백 삭제
  '[' || TRIM(TRAILING FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_TRAILING,  --오른쪽 공백 삭제
  '[' || TRIM(BOTH FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_BOTH, --좌우 공백 삭제
  FROM DUAL;
  
SELECT
  '[' || TRIM('_' FROM '_ _Oracle_ _') || ']' AS TRIM,
  '[' || TRIM(LEADING '_' FROM '_ _ Oracle_ _') || ']' AS TRIM_LEADING,
  '[' || TRIM(TRAILING '_' FROM  '_ _ Oracle_ _') || ']' AS TRIM_TRAILING,
  '[' || TRIM(BOTH '_' FROM '_ _ Oracle_ _') || ']' AS TRIM_BOTH
  FROM DUAL;

예제 2)

SELECT
    '[' || TRIM(' _ ORACLE_ ') || ']' AS TRIM,
    '[' || LTRIM(' _ ORACLE_ ') || ']' AS LTRIM,
    '[' || LTRIM('<_ ORACLE_>', '_<') || ']' AS LTRIM_2,
    '[' || RTRIM(' _ ORACLE_ ') || ']' AS RTRIM,
    '[' || RTRIM('<_ ORACLE!d_>', '_>') || ']' AS RTRIM_2
FROM DUAL;