[SQL] 단일행 함수

 

[단일행 함수]

한번에 하나씩 처리하는 함수

[문자함수]

INITCAP : 첫글자만 대문자로 변환
LOWER : 전부 소문자로 변환
UPPER  : 전부 대문자로 변환
LENGTH  : 문자열 길이 출력
LENGTHB  :  문자열 길이의 바이트값 출력
CONCAT ('문자열1','문자열2') :  문자열 결합 출력 (|| 연산자와 동일)

 

☆*SUBSTR ('문자열' or 컬럼명, 시작위치, 추출할자릿수) :  특정 문자만 추출

마이너스 붙이면 오른쪽 -> 왼쪽 검색 후 왼쪽 -> 오른쪽으로 글자 수 세어냄

 

SUBSTRB ('문자열' or 컬럼명, 시작위치, 추출할바이트수)   :  특정 바이트만 추출

 

INSTR( '문자열' or 컬럼명, 찾는글자,시작위치,몇번째인지(기본값1)) :  특정 문자의 위치 추출

시작위치, 찾으려는위치값 생략 가능 (자동 1)

INSTRB  :  특정 문자 위치의 바이트 값 추출 
LPAD ( '문자열' or 컬럼명,자릿수, '채울문자')  : 왼쪽으로 특정 문자를 채움
RPAD  : 오른쪽으로 특정 문자를 채움
LTRIM  ('문자열' or 컬럼명,'제거할문자')  :  왼쪽의 특정 문자 삭제
RTRIM  :  오른쪽의 특정 문자 삭제

 

☆* REPLACE  ('문자열' or 컬럼명,'문자1', '문자2')   : A 를 B 로 치환

REGEXP_REPLACE  : 특정 패턴을 찾아 치환
REGEXP_INSTR  : 특정 패턴의 시작위치를 반환
REGEXP_SUBSTR  : 특정 패턴을 찾아 반환
REGEXP_LIKE  : 특정 패턴을 찾아 반환
REGEXP_COUNT  : 특정 패턴 횟수를 반환

 

 

[숫자 관련 함수]

ROUND(숫자,출력을 원하는 자릿수) :  반올림
TRUNC (숫자, 원하는 자릿수) : 숫자 버림
MOD  :  나머지 값
CEIL  :  가장 근접한 큰 정수
FLOOR  :  가장 근접한 작은 정수
POWER(숫자1,숫자2) :  1의 숫자 2승


[날짜 관련 함수]

 

날짜 + 숫자 = 날짜

날짜 - 숫자 = 날짜

날짜 - 날짜 = 숫자

SYSDATE : 시스템의 현재 날짜와 시간
MONTHS_BETWEEN  : 두 날짜 사이의 개월수 (결과 숫자)

ADD_MONTHS  : 주어진 날짜에 개월을 더함
NEXT_DAY(sysdate,'월')  : 주어진 날짜를 기준으로 돌아오는 날짜
LAST_DAY  : 주어진 날짜가 속한 달의 마지막 날짜
ROUND  : 날짜 반올림

낮 12:00 기준 , 오후 인 경우 올림
TRUNC  :  날짜 버림

무조건 당일 처리


[형변환함수]

 

CHAR   : 고정길이 문자
VARCHAR2   : 변하는 길이 문자
NUMBER   : 숫자
DATE   : 7바이트 
LONG   : 가변길이 문자 2GB
CLOB   : 가변길이 문자 4GB
BLO   : 가변길이 바이너리 4GB
RAW : 원시 이진 데이터 2000바이트
LONG RAW : 원시 이진 데이터 2GB
BFILE : 외부 파일에 저장된 데이터 4GB

 

 

[자동형변환 (묵시적 형변환)]

자동으로 바꿔주는 형변환

속도가 느려질 수 있음!!

 

 

[수동형변환 (명시적 형변환)]

 

[ 숫자 <-> 문자 <-> 날짜 ]

 

☆*TO_CHAR(원래날짜,'원하는모양')

숫자를 문자로, 날짜를 문자로

• YYYY 연도 4자리 표기
• RRRR 연도 4자리 표기 (Y2K 버그로 파생된)
• YY 연도 끝을 2자리만
• RR 연도를 마지막 2자리
• YEAR 연도의 영문 이름 전체
• MM 월을 숫자 2자리로
• MON 월을 뜻하는 영어
• MONTH 월을 뜻하는 이름 전체
• DD 일을 숫자 2자리로
• DAY 요일
• DDTH 몇번째 날
• HH24 하루 24시간
• HH 하루12시간
• MI 분
• SS 초

 

• 9  : 9의 개수만큼 자리수
• 0  : 빈자리를 0으로 채움
• $  : $ 표시 붙여서 표시
• .  :  소수점 이하를 표시
• ,  : 천단위 구분 기호 표시

 

 

☆*TO_NUMBER()

문자를 숫자로 변환

ASCII()  : 일반 문자를 변환할때

 

☆*TO_DATE()

날짜처럼 생긴 문자를 날짜로 변환

 

[일반함수]

 

NVL (컬럼, 치환할 값)  : 널을 만나면 다른 값으로 치환
NVL2 (COL1,COL2,COL3) : 널 값인 경우, 아닐경우 구분해서 치환

 

☆* DECODE () : IF 문을 SQL 안으로 가져온 함수

DECODE (A,B,'1',NULL)  -- 마지막 NULL 생략 ok

DECODE (A,B,'1','2')

DECODE (A,B,'1',C,'2','3')
A가 B면 1 C면 2 둘다 아니면 3

DECODE (A,B,DECODE(C,D,'1',NULL))
A 가 B일때, C 면 '1' , D면 NULL 출력

DECODE (A,B,DECODE(C,D,'1','2'))
A가 B일때, C가 D면 1 아니면 2

DECODE (A,B,DECODE(C,D,'1','2'),'3)
A가 B일때, C가 D면 1, 아니면 2, B가 아니면 3

☆*  CASE (조건) THEN WHEN ELSE END : = 문을 처리할 때 사용

표현식 내부에 콤마가 사용되지 않는다

 

= 조건 인 경우

CASE() WHEN '02' THEN 'SELOUL'
		WHEN	THEN
        		ELSE
                
		END "LOC"

!= 조건 인 경우

--between 을 이용해서 범위 지정해서 사용!
CASE() WHEN JIMIN BETWEEN '01' AND '03' THEN 'SELOUL'
		WHEN ~~~	THEN ~~~~
        		ELSE ~~~
                
		END ~~

☆*   [ 정규식 Regular Expression ]

편리하고 강력한 검색을 가능하게 해주는 정규식 !

 

^ (캐럿) : 해당 문자로 시작하는 line 출력
$ (달러)  :  해당 문자로 끝나는 line 출력
.  : (. -> 1 character) 'S....E'  S로 시작하여 E 로 끝나는 line 
*  : 모든 이라는 뜻, 글자수가 0[]  :  해당 문자에 해당하는 한 문자
[^]  : 해당 문자에 해당하지 않는 한 문자 

[:문자클래스:] 형태로 사용! 

{} : 앞문자가 나타나는 횟수,범위

캡처 그룹(capture groups):  즉, 정규 표현식에서 괄호 ()로 묶은 부분에 해당하는 매칭된 텍스트 (=치환)

?: 비탐욕적(Non-greedy) 수량자를 의미합니다. 가능한 한 적게 반복하려고 합니다. 마침표(.)가 0번 또는 1번 나올 수 있음을 의미

/ ... /: 슬래시 /는 정규 표현식의 시작과 끝을 나타냅니다.

 

REGEXP_LIKE 함수 : 특정 패턴과매칭되는 결과 검색 

'[a-z]'

'[A-Z]'

'[a-zA-Z]'

'[a-z]  '

'[a-z] [0-9]'

'[[:space:]]'

'[A-Z]{2}'

'[A-Z]{3}' 
'[A-Z]{4}'

'[[:upper:]]' -- 대문자 전부

'^[A-Za-z]' -- 첫 시작 문자 지정

'^[a-z]^[0-9]' -- 소문자나 숫자로 시작하는

'^M(a|o)' -- 조건 연결 가능 |

'[a-z]$'
'^[[:alpha:]]$' -- 소문자로 끝나는 행


'^[^a-z]' -- 대괄호 문자가 아닌 다른 것
'^[^09a-z]' -- 시작이 소문자도 숫자도 아닌 행

WHERE NOT REGEXP_LIKE(,'[a-z]') -- 소문자가 들어있는 모든 행 제거 NOT

'[0-9]{2}\)[0-9]{4}' --국번
'...r.' -- 4번째 자리 소문자 있는 행
'^[10]{2}\.[10]{2}\.[10]{2}'   --[10] 1,0 중 하나
'!' 
'\?'
'\*' --메타문자 escape 문자

 

REGEXP_REPLACE 함수 : 특정 문자로 변경

'[[:digit:]]','*'  -- 숫자부분 변경
'[0-9]','\1-*' -- 숫자뒤에 -*추가 
'\.',''  -- . 지우기
'\.','\',1,1 -- 첫번째만 \로 변경

'( ){1,}',''  -- 공백 이 2칸 이상인 것 찾기
'&id',( ){1,}','' -- 모든 공백주의
LOWER(Regexp_replace('&id',( ){1,}','') -- 소문자로도 변경

'([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})','\1-\2-\3'
--데이터를 2012-03-23 으로 변경

 

REGEXP_SUBSTR 함수

'[^ ]+[DEF]' -- 공백이 아니고 DEF인 부분 추출
'/([[:alnum:]]+\.?){3,4}?'),'/')  -- 홈페이지 http 제거
'[^:]+',1,3  -- : 기준으로 3번째 문자열 출력

substr 확장판 

 

 

REGEXP_COUNT 함수
regexp_count(컬럼,찾을문자,(검색위치지정))

regexp_count(text,'.') -- . : 모든 것
regexp_count(text,'\.') -- . 점 하나