단일 행 함수는 여러 건의 데이터를 한꺼번에 처리하는 것이 아니라 한번에 하나씩 처리하는 함수
복수 행 함수는 여러 건의 데이터를 동시에 입력받아서 결과값 1건을 만들어주는 함수
1. 문자함수
INITCAP 입력값의 첫 글자만 대문자로 변환
INITCAP('abcd') → Abcd
LOWER 입력값을 전부 소문자로 변환
LOWER('ABCD') → abcd
UPPER 입력값을 전부 대문자로 변환
UPPER('abcd') → ABCD
LENGTH 입력된 문자열의 길이 값을 출력
LENGTH('한글') → 2
LENGTHB 입력된 문자열의 길이의 바이트 값을 출력
LENGTHB('한글') → 4
CONCAT 두 문자열을 결합해서 출력, ||연산자와 동일
CONCAT('A','B') → AB
SUBSTR 주어진 문자에서 특정 문자만 추출
SUBSTR('ABC',1,2) → AB
SUBSTR('ABC',-1,2) → BC
시작위치를 -(마이너스) 로 하게되면 뒤에서부터 자릿수를 계산해서 문자를 추출해낸다.
SUBSTRB 주어진 문자에서 특정 바이트만 추출
SUBSTRB('한글',1,2) → 한
INSTR 주어진 문자에서 특정 문자의 위치 추출
INSTR('A*B#','#') → 4
INSTR('A*B*C*','*',-4,1) → 2
SUBSTR과 다르게 시작위치를 -(마이너스) 로 하게 되면 뒤에서부터 좌측으로 찾는다.
INSTRB 주어진 문자에서 특정 문자의 위치 바이트 값 추출
INSTRB('한글로','로') → 5
LPAD 주어진 문자열에서 왼쪽으로 특정 문자를 채움
LPAD('love',6','*') → **love
RPAD 주어진 문자열에서 오른쪽으로 특정 문자를 채움
RPAD('love',6,'*') → love**
LTRIM 주어진 문자열에서 오른쪽의 특정 문자를 삭제함
LTRIM('*love','*') → love
RTRIM 주어진 문자열에서 왼쪽의 특정 문자를 삭제함
RTRIM('love*','*') → love
REPLACE 주어진 문자열에서 A를 B로 치환함
REPLACE('AB','A','E') → EB
REGEXP_REPLACE 주어진 문자열에서 특정 패턴을 찾아 치환함
REGEXP_INSTR 주어진 문자열에서 특정 패턴을 시작 위치를 반환
REGEXP_SUBSTR 주어진 문자열에서 특정 패턴을 찾아 반환함
REGEXP_LIKE 주어진 문자열에서 특정 패턴을 찾아 치환함
REGEXP_COUNT 주어진 문자열에서 특정 패턴의 횟수를 반환
2. SQL문장에서 정규식 사용하기 (10g부터 추가됨)
정규식이란 유닉스에서 검색을 할 때 주로 사용되는 기술로 다양한 메타 문자들을 사용하여 검색 방법을 확장한 것
정규식 방법
^ pattern으로 시작하는 line 출력
'^pattern'
$ pattern으로 끝나는 line 출력
'pattern$'
. p로 시작하여 n으로 끝나는 line (. 1 → character)
'p....n'
* 모든 이라는 뜻, 글자수가 0일 수 도 있음
'[a-z]*'
[] pattern에 해당하는 한 문자
'[p]pattern'
[^] pattern에 해당하지 않는 한 문자
'[^a-z]'
(1) REGEXP_LIKE 함수
LIKE 함수처럼 특정 패턴과 매칭되는 결과를 검색하는 함수
1) 특정 문자나 숫자를 포함하는 결과 출력
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z][0-9]') ;
2) 공백을 한 칸 포함하는 경우
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z] [0-9]') ;
3) 공백이 여러개일 경우 찾기
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z]?[0-9]') ;
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z]*[0-9]') ;
4) 연속적인 글자 수 지정하기
대문자 연속 3글자
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[A-Z]{3}') ;
소문자 연속 3글자
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z]{3}') ;
숫자 연속 3글자
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[0-9]{3}') ;
영문 대문자와 숫자가 함께 오는데 영문 대문자 3글자가 먼저 옴
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[A-Z][0-9]{3}') ;
영문 대문자와 숫자가 함께 오는데 숫자 3글자가 먼저 옴
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[0-9][A-Z]{3}') ;
5) 시작되는 문자와 끝나는 문자 지정하기
시작 문자 ^ (캐럿)
끝나는 문자 $ (달러)
숫자로 시작
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '^[0-9]') ;
소문자나 숫자로 시작
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '^[a-z]|^[0-9]') ;
소문자로 끝나는
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '[a-z]$') ;
숫자로 시작하지 않는
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '^[^0-9]') ;
소문자로 시작하지 않는
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '^[^a-z]') ;
'A'나 1을 포함
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, 'A|1') ;
'z'나 1을 포함
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, 'z|1') ;
6) 특정 조건이 아닌 경우 출력하기
대괄호 안쪽에 ^(캐럿) 을 넣을 경우 작동하지 않는다.
그러나 첫 글자가 특정 조건이 아닐 경우는 ^[^a-z] 로 할 경우는 정상작동한다.
특정조건이 아니 경우를 모두 출력하려면 NOT을 사용한다.
영문자 대,소문자를 포함하지 않는 모든 행을 출력
SELECT *
FROM reg_test
WHERE NOT REGEXP_LIKE (text, '[a-z]|[A-Z]') ;
숫자를 포함하지 않는 모든 행을 출력
SELECT *
FROM reg_test
WHERE NOT REGEXP_LIKE (text, '[0-9]') ;
7) 특수문자 찾기
'?' 문자가 들어가는 행 출력
SELECT *
FROM reg_test
WHERE REGEXP_LIKE (text, '\?') ;
'?' 문자가 들어가지 않는 행 출력
SELECT *
FROM reg_test
WHERE NOT REGEXP_LIKE (text, '\?') ;
(2) REGEXP_REPLACE 함수
REPLACE 함수를 확장한 개념으로 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수
문법
REGEXP_REPLACE (source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param]]]]
)
첫 번째 인수인 source 는 원본 데이터를 의미한다. 컬럼명이나 문자열이 올 수 있다.
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB
두 번째 인수인 pattern은 찾고자 하는 패턴을 의미한다. 512 바이트까지의 정규 표현식을 사용하며
CHAR, VARCHAR2, NCHAR, NVARCHAR2
세 번째 인수인 replace_string 은 변환하고자 하는 형태이다.
두 번째 패턴에 일치하는 문자 (또는 문자열) 를 찾아서 세번째 모양으로 변환하란는 의미이다.
네번째 인수인 position 은 검색 시작 위치를 지정한다. 기본값은 1
다섯번째 인수인 occurrence 는 패턴과 일치가 발생하는 횟수를 의미한다.
0 은 모든 값을 대체하고 n 번째 발생하는 문자열을 대입한다.
여섯번째 인수인 match_parameter 는 기본값으로 검색되는 옵션을 바꿀 수 있다.
c: 대소문자 구분 검색
i: 대소문자 구분하지 않고 검색
m: 검색 조건을 여러 줄로 줄 수 있다.
ci 가 중복으로 설정되면 마지막 값으로 적용.
1) 모든 숫자를 특수기호로 변경하기
SELECT text,
REGEXP_REPLACE (text, '[[:digit:]]', '+') "숫자→기호"
FROM reg_test ;
2) 특정 패턴을 찾아서 패턴을 추가하기
SELECT text,
REGEXP_REPLACE (text, '[0-9]', '\1*') "패턴추가"
FROM reg_test ;
3) student 테이블에서 지역번호가 2자리이고 전화국번이 4자리인 전화번호를 가진 학생의 이름과 전화번호 출력
SELECT name, tel
FROM reg_test ;
WHERE REGEXP_REPLACE (tel, '\d{2})\)(\d{4})\-(\d{4}', '\2') > '5000' ;
4) student 테이블에서 제1전공이 101번인 학생의 이름과 이름 사이에 -를 넣어서 출력
SELECT name,
RTRIM(REGEXP_REPLACE (name, '(.)', '\1-'),' '-') "변경 후"
FROM student
WHERE deptno1 = 101
5) 사용자에게 입력받은 문자 가운데 공백이 여러개 있을 경우 공백을 제거하는 방법
SELECT REGEXP_REPLACE ('abc 123', '( ){1,}', '')
FROM dual ;
6) 사용자가 회원검색을 할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도 공백이 있을 때 모든 공백을 제거해야 할 경우
SELECT studno, name, id
FROM student
WHERE id = LTRIM(REGEXP_REPLACE('&id', '( ){1,}', ''), ' ') ;
old 와 new 줄을 보이지 않게 하려면 SET verify off 입력
7) 특정 문자열을 다른 형태로 바꿀 때
SELECT REGEXP_REPLACE ('20120324', '([[:digit]]{4})({{:digit:]]{2})([[:digit:]]{2})',\1:\2:\3')
FROM dual ;
(3) REGEXP_INSTR 함수
특정패턴이 출현하는 첫 위치 값을 반환하는 함수
1) 특정 문자의 위치를 찾는 방법
text 중에서 '?' 의 위치를 찾아내는 예
SELECT text, REGEXP_INSTR(text, '\?') "? 위치"
FROM reg_test ;
2) 여러가지 옵션으로 검색하기
SELECT text,
REGEXP_INSTR(text, '[A-Z]{3}', -- 표현식
1, -- 검색시작 위치
1, -- 찾는 패턴 순번
0 -- offset
) "offset 0",
REGEXP_INSTR(text, '[A-Z]{3}', -- 표현식
1, -- 검색시작 위치
1, -- 찾는 패턴 순번
1 -- offset
) "offset 1",
REGEXP_INSTR(text, '[A-Z]{3}', -- 표현식
1, -- 검색시작 위치
1, -- 찾는 패턴 순번
0, -- offset
'i' -- 대소문자 구분 안함
) "대소문자"
FROM reg_test ;
(4) REGEXP_SUBSTR 함수
SUBSTR 함수의 확장판으로 특정 패턴에서 주어진 문자를 추출해 내는 함수
1) professor 테이블에서 hpage 주소가 있는 교수들만 조사해서 이름과 hpage 를 출력
SELECT name,
LTRIM(REGEXP_SUBSTR(hpage,'/([[:alnum:]]+\.?){3,4}?'),'/') "hpage"
FROM professor
WHERE hpage IS NOT NULL ;
2) professor 테이블에서 교수의 이름과 메일주소를 출력하되 메일주소는 @ 뒤에 있는 주소만 출력
SELECT name,
LTRIM(REGEXP_SUBSTR(email, '@([[:alnum:]]+\.?){3,4}?'),'@') "email"
FROM professor ;
3. 11g에서 추가된 정규식 함수
(1) REGEXP_COUNT 함수: 특정문자의 개수를 세는 함수
1) 주어진 문자열에서 소문자 'a'가 몇개인지 찾아주는 함수
SELECT text,
REGEXP_COUNT(text, 'a')
FROM reg_test ;
2) 검색위치를 3으로 지정해서 3번째 문자 이후부터 해당 소문자 'a'가 나오는 개수를 세는 예
SELECCT text,
REGEXP_COUNT(text, 'a', 3)
FROM reg_test ;
3) 대소문자 구분 여부를 테스트 (i 옵션은 대소문자 무시)
SELECT text,
REGEXP_COUNT(text, 'A',) "R1",
REGEXP_COUNT(text, 'A', 1, 'i') "R2",
FROM reg_test ;
4) 탈출문자(escape)를 사용하는 예 (탈출문자 없이 .(점) 은 모든 것, 탈출문자 있을 경우 .(점)으로 인식)
SELECT text,
REGEXP_COUNT(text, '.') "R1",
REGEXP_COUNT(text, '\.') "R2"
FROM reg_test ;
5) 어떤 문자를 검색할 때 사용하는 방법
SELECT text,
REGEXP_COUNT(text, 'aa') "R1",
REGEXP_COUNT(text, 'a{2}') "R2",
REGEXP_COUNT(text, '(a)(a)') "R3"
FROM reg_test ;
4. 11g REGEXP_SUBSTR 추가 기능
(Sub Expression 사용하기)
SELECT hpage,
REGEXP_SUBSTR(hpage, '(\.)([a-z]+)(\.)' , -- 찾고싶은 패턴
1, -- 검색 시작 위치
1, -- 검색 occurrece
'i', -- 대소문자 구분 없음
1 -- 11g 부터 추가된 기능
) "REGEXP"
FROM professor
WHERE hpage IS NOT NULL
6번라인의 숫자부분이 Sub Expression 이다. 위 함수를 수행하면 결과가 '.abc.' 등으로 나오는데 첫번째를 출력하라고 해서
REGEXP 부분에 전부 .(점) 만 나오게 된 것이다.
이 부분의 숫자를 2로 변경하면 영어부분이 출력된다. 3으로 변경하면 당연히 .(점) 만 출력될 것이다.
5. 숫자 함수
ROUND 주어진 숫자를 반올림 한 후 출력함
TRUNC 주어진 숫자를 버림 한 후 출력함
MOD 주어진 숫자를 나누기 한 후 나머지 값 출력함
CEIL 주어진 숫자와 가장 근접한 큰 정수 출력함
FLOOR 주어진 숫자와 가장 근접한 작은 정수 출력함
POWER 주어진 숫자 1의 숫자 2승을 출력함
6. 날짜 함수
SYSDATE 시스템의 현재 날짜와 시간
MONTHS_BETWEEN 두 날짜 사이의 개월 수 ( 두 날짜 중 큰 날짜를 먼저 써야 양수가 나온다. )
ADD_MONTHS 주어진 날짜에 개월을 더함
NEXT_DAY 주어진 날짜를 기준으로 돌아오는 날짜 출력
LAST_DAY 주어진 날짜가 속한 달의 마지막 날짜 출력
ROUND 주어진 날짜를 반올림 ( 정오가 지나지 않으면 당일로, 지나면 익일로 출력한다. )
TRUNC 주어진 날짜를 버림 ( 무조건 당일로 출력한다. )
7. 형 변환 함수
CHAR(n) 고정 길이의 문자를 저장한다. 최대값은 2000 bytes
VARCHAR2(n) 변하는 길이의 문자를 저장한다. 최대값은 4000 bytes
NUMBER(p,s) 숫자 값을 저장한다. p는 전체 자릿수로 1~38자리까지 가능하고 s는 소수점 이하 자릿수로 -84~127자리가 가능하다.
DATE 총 7 bytes로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장할 수 있다.
LONG 가변 길이의 문자를 저장하며 최대 2GB까지 저장할 수 있다.
CLOB 가변 길이의 문자를 저장하며 최대 4GB까지 저장할 수 있다.
BLOB 가변 길이의 바이너리 데이터를 최대 4GB까지 저장할 수 있다.
RAW(n) 원시 이진 데이터로 최대 2000 bytes까지 저장할 수 있다.
LONG RAW(n) 원시 이진 데이터로 최대 2GB까지 저장할 수 있다.
BFILE 외부 파일에 저장된 데이터로 최대 4GB까지 저장할 수 있다.
오라클에서 형 변환은 묵시적(자동) 형변환과 명시적(수동) 형변환이 있다.
숫자처럼 생긴 문자는 오라클이 자동으로 문자를 숫자로 바꾼 후 연산을 수행한다.
묵시적 형 변환은 편한 듯 보이지만 튜닝에서 뜻하지 않게 속도가 느려지는 주범이 될 수 있다.
TO_CHAR TO_CHAR
→ ←
숫자 문자 날짜
← →
TO_NUMBER TO_DATE
TO_CHAR ( 날짜를 문자로 형 변환하기 )
년도
YYYY 연도 4자리로 표현
RRRR 2000년 이후에 Y2K 버그로 인해 등장한 새로운 날짜 표기법
YY 연도를 끝의 2자리만 표시
YEAR 연도의 영문 이름 전체를 표시
월
MM 월을 숫자 2자리로 표현
MON 월을 뜻하는 영어 3글자로 표시
MONTH 월을 뜻하는 영어 이름 전체를 표시
일
DD 일을 숫자 2자리로 표시
DAY 요일에 해당하는 영어 명칭 표시
DDTH 몇 번째 날인지 표시
시간
HH24 하루를 24시간으로 표시
HH 하루를 12시간으로 표시
MI 분
SS 초
TO_CHAR ( 숫자를 문자로 형 변환하기 )
9 9 하나당 1자리를 의미
TO_CHAR(1234,'99999') → 1234
0 빈 자리를 0으로 표시
TO_CHAR(1234,'099999') → 001234
$ $ 표시를 붙여서 표시
TO_CHAR(1234,'$9999') → $1234
. 소수점 이하를 표시
TO_CHAR(1234,'9999,99') → 1234.00
, 천 단위 구분 기호를 표시
TO_CHAR(12345,'99,999') → 12,345
8. 일반 함수
NVL(sal, 0) sal 칼럼이 null 일 경우 0으로 치환
NVL(sal, 100) sal 칼럼이 null 일 경우 100으로 치환
NVL(job, '무직') job 칼럼이 null 일 경우 '무직' 으로 치환
NVL(hiredate, 'sysdate') hiredate 값이 없을 경우 sysdate 날짜로 치환
NVL2(bonus, pay*12, pay*0) bonus 칼럼이 null이 아니면 pay*12를 출력하고 null일 경우 pay*0을 출력한다.
DECODE( A, B, '1', null ) A가 B라면 1을 출력하고, 아니라면 null을 출력한다.
DECODE( A, B, '1', C, '2', '3' ) A가 B라면 1을, A가 C라면 2를, 둘다 아니라면 3을 출력한다. ( 반복 가능 )
DECODE( A, B, DECODE(A, C, '1', '2')'3') A가 B일 경우 중 A가 C를 만족하면 1을 A가 C가 아닐 경우 2를 A가 B가 아닐 경우 3을 출력
DECODE는 오라클에서만 사용되는 함수로 IF문을 사용해야 하는 조건문을 처리할 수 있다.
CASE 조건
WHEN 결과1 THEN 출력1
WHEN 결과2 THEN 출력2
ELSE 출력3
END "칼럼명"
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저