[불친절한SQL] 기초쿼리, 문자 함수

오라클 데이터베이스는 다양한 내장 SQL함수를 제공한다.

이번 장에서는 단일 행 함수를 살펴보도록 하겠다.

함수	설명	장
단일 행 함수	단일 행을 입력받아 단일 행을 반환하는 함수	6장
집계 함수	다중 행을 입력받아 단일 행을 반환하는 함수	9장
분석 함수	다중 행을 입력받아 다중 행을 반환하는 함수	14장
모델 함수	MODEL 절에서 사용하는 함수	26장

 

○ 문자 함수

문자 값을 조작하는 함수이다.

 

CHR 함수

n에 해당하는 데이터베이스 캐릭터 셋의 문자 값을 반환한다.

문자로 입력할 수 없는 특수 문자를 입력할 때 사용할 수 있다.

아래 CHR(10) 함수는 줄 바꿈 문자를 의미한다.

SELECT 'AB' || CHR(10) || ' C' AS c1 FROM DUAL;

 

LOWER 함수

char를 소문자로 변경한다.

SELECT LOWER('abC') AS c1 FROM DUAL;

 

UPPER 함수

char를 대문자로 변경한다.

SELECT UPPER('abC') AS c1 FROM DUAL;

 

INITCAP 함수

char에 포함된 단어의 첫 글자는 대문자, 나머지는 소문자로 변경한다.

공백이나 숫자와 알파벳이 아닌 문자로 구분한다.

SELECT INITCAP ('abC de') AS c1, INITCAP('abC,de') AS c2 FROM DUAL;

 

LPAD 함수, RPAD 함수

expr의 길이를 좌측(Left) 또는 우측(Right) 으로 n 만큼 늘린 후, 늘어난 공간을 expr2로 반복해서 채운다.

expr2의 기본값은 공백이다.

expr1이 NUILL이면 NULL을 반환한다

LPAD (expr1, n [, expr2])
RPAD (expr1, n [, expr2])

SELECT LPAD('AB', 3) AS c1, LPAD('AB', 1) AS c2, LPAD('AB', 5, '12') AS c3 FROM DUAL;

 

SELECT RPAD('AB', 3) AS c1, RPAD('AB', 1) AS c2, RPAD('AB', 5, '12') AS c3 FROM DUAL;

 

LTRIM 함수, RTRIM 함수

char의 좌측(Left) 또는 우측(Right)부터 set에 포함된 문자를 제거한다.

char는 한 문자씩 set과 비교되며, set에 포함되지 않는 문자를 만나면 제거가 중단된다.

set의 기본값은 공백(' ')이다.

LTRIM(char [,set])
RTRIM(char [,set])

SELECT LTRIM(' A ') AS c1, LTRIM('ABC', 'BA') AS c2, LTRIM('ABC', 'BC') AS c3 FROM DUAL;

 

TRIM 함수

trim_source의 좌측이나 우측이나 양측에서 trim_character를 제거한다.

trim_character이 아닌 문자를 만나면 제거를 멈춘다.

위치의 기본값은 BOTH이다.

trim_character은 한 문자만 지정할 수 있으며, 기본값은 공백이다.

TRIM ([{{LEADING | TRAILING | BOTH} [trim_character] || trim_character} FROM] trim_source)

SELECT TRIM( LEADING 'A' FROM 'AAB ') AS c1, TRIM( LEADING FROM 'AAB ') AS c2
     , TRIM(TRAILING 'B' FROM 'AAB ') AS c3, TRIM(TRAILING FROM 'AAB ') AS c4
     , TRIM(    BOTH 'A' FROM 'AAB ') AS c5, TRIM(    BOTH FROM 'AAB ') AS c6
     , TRIM(         'B' FROM 'AAB ') AS c7, TRIM(              'AAB ') AS c8
  FROM DUAL;

 

SUBSTR 함수

char를 position 위치에서 우측으로 substring_length만큼 자른다.

substring_length를 생략하면 끝까지 잘린다.

position이 음수인 경우 끝에서 좌측으로 음수만큼 이동한 위치에서 우측으로 자른다.

position이 char의 길이보다 크면 NULL을 반환한다.

SUBSTR(char, position [, substring_length])

SELECT SUBSTR('123456',  4) AS c1, SUBSTR('123456',  4, 2) AS c1
	 , SUBSTR('123456', -4) AS c3, SUBSTR('123456', -4, 2) AS c4
     , SUBSTR('123456', 7) AS c5
  FROM DUAL;

 

REPLACE 함수

char에 포함된 search_string을 search_string을 replacement_string으로 변경한다.replacement_string의 기본값은 NULL이다.정규식을 사용하면 복잡한 REPLACE 쿼리를 간결하게 작성할 수 있다.

REPLACE(char, search_string [, replacement_string])

SELECT REPLACE('ABCABC', 'AB'       ) AS c1
     , REPLACE('ABCABC', 'AB', '1'  ) AS c2
     , REPLACE('ABCABC', 'AB', '123') AS c3
  FROM DUAL;

 

TRANSLATE 함수

TRANSLATE 함수는 expr문자와 일치하는 from_string 문자를 대응하는 위치의 to_string 문자로 변환한다.

from_string 문자와 일치하지 않는 문자는 변환하지 않는다.

TRANSLATE(expr, from_string, to_string)

아래 쿼리의 c1열의 B는 NULL, c2열의 B는 공란과 대응한다.

c3열의 3은 일치하는 문자가 없으므로 무시된다.

SELECT TRANSLATE('AAABBC', 'AB', '1'  ) AS c1
     , TRANSLATE('AAABBC', 'AB', '1 ' ) AS c2
     , TRANSLATE('AAABBC', 'AB', '123') AS c3
  FROM DUAL;

 

INSTR 함수

string의 position에서 우측으로 occurrence번째 substring의 시작 위치를 반환한다.

position과 occurrence의 기본값은 1이다.

SELECT INSTR('ABABABAB', 'AB'       ) AS c1
     , INSTR('ABABABAB', 'AC'       ) AS c2, INSTR('ABABABAB', 'AB',  9   ) AS c3
     , INSTR('ABABABAB', 'AB',  1, 2) AS c4, INSTR('ABABABAB', 'AB',  3, 2) AS c5
     , INSTR('ABABABAB', 'AB', -1, 2) AS c6, INSTR('ABABABAB', 'AB', -3, 2) AS c7
  FROM DUAL;

 

LENGTH 함수

char의 길이를 반환한다.

SELECT LENGTH('AB') AS c1, LENGTH('AB  ') AS c2 FROM DUAL;

아래 쿼리의 c2, c3 열은 c1 문자열에 포함된 쉼표(,)의 갯수를 계산한다.

c4 열처럼 정규 표현식을 사용하면 쿼리를 간결하게 작성할 수 있다.

WITH w1 AS (SELECT ',A,B,' AS c1 FROM DUAL)
SELECT c1
     , LENGTH(c1) - LENGTH(REPLACE (c1, ',')) AS c2
     , LENGTH(TRANSLATE(c1, ',' || c1, ','))  AS c3
     , REGEXP_COUNT(c1, ',')                  AS c4
  FROM w1;