본문
- 함수 : 어떤 입력값을 받아서 정해진 루틴에 의해 작업하고 결과값을 만들어 출력해 주는 것.
┌ 단일행 함수 : 한번에 하나씩 처리하는 함수
└ 복수행 함수(=그룹 함수) : 여러 건의 데이터를 동시에 입력
1. 문자 함수
(1) INITCAP 함수 : 첫글자만 대문자로 출력하고 나머지는 소문자로 출력하는 함수 [문법] INITCAP(문자열 또는 칼럼명)
※ 문자열 중간에 공백이 있는 경우 단어의 시작 부분이 대문자로 바뀐다.
(2) LOWER 함수 : 입력되는 값을 전부 소문자로 출력 [문법] LOWER(문자열 또는 칼럼명)
(3) UPPER 함수 : 입력되는 값을 전부 대문자로 출력 [문법] UPPER(문자열 또는 칼럼명)
(4) LENGTH / LENGTHB 함수 : 입력된 문자열의 길이(바이트 수)를 계산하는 함수
[문법] LENGTH/LENGTHB(문자열 또는 칼럼명)
(5) CONCAT 함수 : 문자열 연결 함수(|| 연산자와 동일) [문법] CONCAT('문자열1', '문자열2')
(6) SUBSTR 함수 : 문자열에서 특정 길이의 문자를 추출하는 함수 [문법] SUBSTR('문자열 또는 칼럼명', 시작위치, 글자수)
※ 시작 위치를 -(마이너스)로 하면 뒤에서부터 자리수를 계산
(7) SUBSTRB 함수 : 문자열에서 지정한 바이트수대로 문자를 추출하는 함수
(8) INSTR 함수 : 주어진 문자열이나 칼럼에서 특정 글자의 위치를 찾아주는 함수
[문법] INSTR('문자열 또는 칼럼명', 찾는글자, 시작위치, 몇번째인지(기본값은 1))
(9) LPAD 함수 : 주어진 문자열에서 왼쪽으로 특정 문자를 채워주는 함수
[문법] LPAD('문자열' 또는 칼럼명, 자릿수, 채울 문자)
SCOTT> SELECT id, LPAD(id,20,'$') "LATD" FROM student WHERE deptno1=101 ;
ID LATD
---------- -----------------------
75true $$$$$$$$$$$$$$75true
bingo $$$$$$$$$$$$$$$bingo
onejimae $$$$$$$$$$$$onejimae
prettygirl $$$$$$$$$$prettygirl
(10) RPAP 함수 : 주어진 문자열에서 오른쪽으로 특정 문자를 채워주는 함수
[문법] RPAD('문자열' 또는 칼럼명, 자릿수, 채울 문자)
(11) LTRIM 함수 : 주어진 문자열에서 왼쪽의 특정 문자를 삭제하는 함수 [문법] LTRIM('문자열' 또는 칼럼명, '제거할 문자')
(12) RTRIM 함수 : 주어진 문자열에서 오른쪽의 특정 문자를 삭제하는 함수
[문법] RTRIM('문자열' 또는 칼럼명, '제거할 문자')
(13) REPLACE 함수 : 첫번째 문자열에서 문자1이 있을 경우 문자2로 바꾸어 출력하는 함수(문자열 치환)
[문법] REPLACE('문자열' 또는 칼럼명, '문자1', '문자2')
2. SQL 문장에서 정규식 사용하기(10g부터 추가)
- 정규식 : 유닉스에서 검색을 할 때 주로 사용되는 기술로 다양한 메타 문자들을 사용하여 검색 방법을 확장하는 것을 말함.
사용 기호 |
의 미 |
사용 예 |
^ |
Pattern으로 시작하는 line 출력 |
'^pattern' |
$ |
Pattern으로 끝나는 line 출력 |
'pattern$' |
. |
P로 시작하여 n으로 끝나는 line ( . 1 character) |
'p . . . . n' |
* |
모든 이라는 뜻. 글자수가 0 일수도 있음. |
'[a–z]*' |
[] |
Pattern에 해당하는 한 문자 |
'[Pp]attern' |
[^] |
Pattern에 해당하지 않는 한 문자 |
'[^a–m]attern' |
(1) REGEXP_LIKE 함수 : like 함수 처럼 특정 패턴과 매칭되는 결과를 검색해내는 함수
ex) [특정 문자나 숫자를 포함하는 결과 출력하기] SELECT * FROM reg_test WHERE REGEXP_LIKE(text, '[a-z][0-9]') ;
[공백을 한칸 포함한 경우 찾기] SELECT * FROM reg_test WHERE REGEXP_LIKE(text, '[a-z] [0-9]') ;
[공백이 있는 데이터만 찾고 싶을 경우] SELECT * FROM reg_test WHRER REGEXP_LIKE(text, '[[:space:]]') ;
[공백이 여러 개일 경우 찾기] SELECT * FROM reg_test WHERE REGEXP_LIKE(text, '[a-z]?[0-9]') ;
[연속적인 글자 수 지정하기] SELECT * FROM reg_test WHERE REGEXP_LIKE(text, '[A-Z]{3}') ; 대문자 연속 3개
[시작되는 문자와 끝나는 문자 지정하기] 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 NOT REGEXP_LIKE(text, '[a-z|A-Z]') ;
['?' 문자가 들어가는 행] SELECT * FROM reg_test WHERE REGEXP_LIKE(text, '\?') ;
['?' 문자가 들어가지 않은 행] SELECT * FROM reg_test WHERE NOT REGEXP_LIKE(text, '\?') ;
(2) REGEXP_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 이란 숫자를 주면 n 번째 발생하는 문자열을 대입
- 여섯 번째 match_parameter 는 기본값으로 검색되는 옵션을 바꿀 수 있음
- c : 대소문자를 구분해서 검색합니다.
- i : 대소문자를 구분하지 않고 검색합니다.
- m : 검색 조건을 여러 줄로 줄 수 있습니다.
※ 만약 위 조건 중 c 와 i 가 중복으로 설정되면 마지막에 설정된 값을 사용 즉, ic 가 중복으로 설정되면 c 옵션이 적용
예제1) 모든 숫자를 특수 기호로 변경하기
- 숫자부분을 ‘+’ 기호로 전부 변경
'[[:digit:]]' 부분은 [:문자클래스:]의 형태로 표현
그리고 "문자클래스"에 들어갈 수 있는 내용은 alpha, blank, cntrl, digit, graph, lower, print, space, uppper, xdigit의 종류가 있습니다.
많이 사용되는 것의 예를 들면 [:digit:]는 [0-9]의 의미이고 [:alpha:]는 [A-Za-z] 와 같은 의미
예제2) 특정 패턴을 찾아서 패턴 추가하기
- 숫자를 찾아서 숫자 뒤에 '*'를 추가
(3) REGEXP_INSTR 함수 : 특정 패턴이 출현하는 첫 위치의 값을 반환하는 함수
예제1) 특정 문자의 위치를 찾는 방법
- text중에서 '?'의 위치를 찾아내는 예
(4) REGEXP_SUBSTR 함수 : 특정 패턴에서 주어진 문자를 추출해 내는 함수
예제1) ‘abc* *def %ghi,jkl’ 이란 문자열에서 첫 글자가 공백이 아니고( ’[^ ]’) 그 후에 ‘def 가 나오는 부분을 추출
3. 11g에서 추가된 정규식 함수
(1) REGEXP_COUNT 함수 : 특정 문자의 개수를 세는 함수
예제1) 주어진 문자열에서 소문자 'a'가 몇 개인지 찾기
4. 11g REGEXP_SUBSTR 추가 기능(Sub Expression 사용하기)
- 6번 째 줄의 숫자 부분이 Sub Expression.
위 함수를 수행하면 결과가 '.abc.' 등으로 나오는데 거기서 첫 번째를 출력하라고 해서 REGEXP 부분에 전부 .(점) 만 나옴
이 부분의 숫자를 2로 변경하면 영어부분이 출력됨
서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.52-88
'Oracle > Oracle_SQL' 카테고리의 다른 글
[4장] JOIN(조인) (0) | 2014.10.22 |
---|---|
[3장] SQL 복수 행 함수 (그룹 함수) (0) | 2014.10.22 |
[2장] SQL 단일 행 함수(2) - 숫자함수, 날짜함수, 형변환함수, 일반 함수 (0) | 2014.10.15 |
[1장] SELECT문을 이용하여 원하는 데이터 가져오기 (0) | 2014.10.15 |
오라클 SQL과 PL/SQL / 서진수 지음 (0) | 2014.10.15 |