오라클 SQL함수, 문자함수, UPPER, SUBSTR, SUBSTRB, LTRM, RTRIM, REPLACE, RPAD, LPAD, LENGTH, INSTR, INITCAP, CONCAT, ASCII, CHAR

오라클 SQL함수, 문자함수, UPPER, SUBSTR, SUBSTRB, LTRM, RTRIM, REPLACE, RPAD, LPAD, LENGTH, INSTR, INITCAP, CONCAT, ASCII, CHAR

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=609

 

오라클 SQL함수, 문자함수, UPPER, SUBSTR, SUBSTRB, LTRM, RTRIM, REPLACE, RPAD, LPAD, LENGTH, INSTR, INITCAP, CONCAT, ASCII, CHAR

오라클 SQL함수, 문자함수, UPPER, SUBSTR, SUBSTRB, LTRM, RTRIM, REPLACE, RPAD, LPAD, LENGTH, INSTR, INITCAP, CONCAT, ASCII, CHAR4. SQL 함수 SQL 함수를 크게 나눈다면 단일 행 함수와 복수 행 함수 2가지로 구분 할 수 있다. 단일 행 함수는 각각의 행에 대해 수행 되…

ojc.asia

4. SQL 함수

SQL 함수를 크게 나눈다면 단일 행 함수와 복수 행 함수 2가지로 구분 할 수 있다. 단일 행 함수는 각각의 행에 대해 수행 되어 행 당 하나의 결과를 반환하는 함수이며 복수 행 함수는 여러 개의 행에 적용되어 그룹 당 하나의 결과를 반환하는 함수이다.

집합 함수(Aggreation Function)는 복수 행 함수이고 나머지 함수 문자함수, 숫자함수, 날짜함수, 분석용 함수, 기타함수는 단일 행 함수이다.

4.1 문자함수

ASCII(C) : 인수C의 첫 문자를 ASCII 코드로 변환

CHR(n) : ASCII 코드 n에 대응하는 문자로 변환

CONCAT(c1, c2) : 문자열 c1과 c2를 덧붙인 문자열을 반환

INITCAP(c) : 인수 c의 각 단어의 첫 문자를 대문자로 나머지는 소문자로 변환

INSTR(c1, c2) : 지정한 문자가 위치하는 첫 번째 위치를 반환하는 함수이다. 즉 c1에서 c2가 나타나는 첫 번째 위치를 돌려준다. 지정된 문자가 문자열 내에 없을 때는 0을 RETURN한다.

INSTR(c1, c2, m, n) : c1에서 c2가 나타나는 첫 번째 위치를 돌려주는데 m번째부터 n번째 나타나는 지정한 문자의 위치를 검색한다. m이 음수이면 뒤에서부터 검색한다.

LENGTH(c) : 인수 c의 길이를 돌려준다.(한글도 1로 돌려줌)

LENGTHB(c) : 인수 c의 길이를 돌려준다.(바이트 단위)

LOWER(c) : 인수 c를 소문자로 변환한다.

LPAD(c1, n, c2) : 전체의 길이가 n이며 c1의 왼쪽을 c2로 채운다. 만약 c2가 생략되었다면 공백으로 채운다.

LTRIM(c, [,set]) : 인수c의 왼쪽부터 c가 아닐 때까지 set에 지정된 문자를 제거한다. 만약 set이 지정되어 있지 않으면 공백을 제거한다. 흔히 LTRIM은 set을 생략하여 좌측 공백을 제거하는 용도로 많이 사용된다.

REPLACE(c, a, b) : 인수 c의 문자중에서 a를 b로 바꿈

RPAD(c1, n, c2) : 전체의 길이가 n이며 c1의 오른쪽을 c2로 채운다. 만약 c2가 생략되었다면 공백으로 채운다.

RTRIM(c, [,set]) : 인수c의 오른쪽부터 c가 아닐 때까지 set에 지정된 문자를 제거한다. 만약 set이 지정되어 있지 않으면 공백을 제거한다.

SUBSTR(c, m [,n]) : 인수 c의 m번째부터 n개 문자를 잘라낸다. n이 생략되면 c의 끝까지를 잘라낸다.

SUBSTRB(c, m [,n]) : 인수 c의 m번째부터 n개 문자를 잘라낸다. n이 생략되면 c의 끝까지를 잘라낸다.(byte단위 처리)

TRANSLATE(c, from, to) : 인수 c의 문자열에서 from에 해당 하는 것 들을 to에 해당하는 것으로 바꾼다.

UPPER(c) : 인수 c의 문자열을 대문자로 바꾼다.

ASCII(C) : 인수C의 첫 문자를 ASCII 코드로 변환

CHR(n) : ASCII 코드 n에 대응하는 문자로 변환

SQL> column CHR_TEST format a8 SQL> select ascii('A') as ASCII_TEST, chr(65) as CHR_TEST from dual; ASCII_TEST CHR_TEST ---------- -------- 65 A

DUAL(잠시만요) TABLE : 오라클 데이터베이스가 생성될 때 데이터 딕셔너리에 자동으로 생성되며 그 소유자는 SYS 사용자로서 모든 사용자들의 접근이 허용된다. DUMMY라고 불리는 하나의 칼럼으로 구성되어 있으며 데이터형은 VARCHAR2(1)이다. 하나의 행을 가지며 그 값은 ‘X’이다. DUAL Table은 사용자 데이터가 있는 테이블에서 유래하지 않은 상수 값, 의사열(pseudo-column), 표현식 등의 값을 단 한번만 돌려 거나 현재 날짜, 시각을 알고자 할 때 이용된다. 즉 일시적인 산술, 날짜 연산 등에 주로 이용된다.

SQL> desc dual; 이름 널? 유형 ----------------------------------------- -------- -------------- DUMMY VARCHAR2(1) SQL> select sysdate from dual; SYSDATE -------- 03/08/12 SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------- 03/08/12 00:07:33.681000 +09:00 SQL> select 3*4 from dual; 3*4 ---------- 12

CONCAT(c1, c2) : 문자열 c1과 c2를 덧붙인 문자열을 반환

SQL> select concat('OracleJava ', 'Community') from dual; CONCAT('Oracle' -------------- OracleJAvaCommunity

INITCAP(c) : 인수 c의 각 단어의 첫 문자를 대문자로 나머지는 소문자로 변환

SQL> select initcap('oracle jAVA') from dual; INITCAP('OR ----------- Oracle Java

INSTR(c1, c2) : 지정한 문자가 위치하는 첫 번째 위치를 반환하는 함수이다. 즉 c1에서 c2가 나타나는 첫 번째 위치를 돌려준다. 지정된 문자가 문자열 내에 없을 때는 0을 RETURN한다.

INSTR(c1, c2, m, n) : c1에서 c2가 나타나는 첫 번째 위치를 돌려주는데 m번째부터 n번째 나타나는 지정한 문자의 위치를 검색한다. m이 음수이면 뒤에서부터 검색한다.

SQL> select instr('안녕JCLEE','JCLEE') from dual; INSTR('안녕JCLEE','JCLEE') -------------------------- 3 -- 참고로 instrb 라는 함수도 살펴보자.이 함수는 바이트 단위로 위치를 찾으므로 위의 결과와는 틀리다.(한글은 2바이트로 간주) SQL> select instrb('안녕JCLEE','JCLEE') from dual; INSTRB('안녕JCLEE','JCLEE') --------------------------- 5 -- 아래 예문은 4번째 문자부터 검색하여 ‘T’가 첫번째 나타나는 위치를 돌려주는 것이다. SQL> select instr('안녕TATATA','T',4,1) from dual; INSTR('안녕TATATA','T',4,1) --------------------------- 5 -- 아래 예문은 4번째 문자부터 검색하여 ‘T’가 두번째 나타나는 위치를 돌려주는 것이다. SQL> select instr('안녕TATATA','T',4,2) from dual; INSTR('안녕TATATA','T',4,2) --------------------------- 7 -- 지정한 문자가 없을 때는 0을 리턴 SQL> select instr('ORACLEJAVA COMMUNITY','C#') from dual; INSTR('ORACLEJAVACOMMUNITY','C#') --------------------------------- 0 -- ‘a’문자를 찾는데 3번째부터 찾고 a가 2번째 나타나는 것을 찾는다. SQL> select instr('OracleJavaCommunity','a',3, 2) from dual; INSTR('ORACLEJAVACOMMUNITY','A',3,2) ------------------------------------ 8 -- ‘a’문자를 찾는데 1번째부터 찾고 a가 2번째 나타나는 것을 찾는다. SQL> select instr('OracleJavaCommunity','a',1, 2) from dual; INSTR('ORACLEJAVACOMMUNITY','A',1,2) ------------------------------------ 8 -- ‘a’문자를 찾는데 4번째부터 찾고 a가 2번째 나타나는 것을 찾는다. SQL> select instr('OracleJavaCommunity','a',4, 2) from dual; INSTR('ORACLEJAVACOMMUNITY','A',4,2) ------------------------------------ 10 -- 뒤에서부터 검색을 하며, 뒤에서부터 10번째부터 ‘a’를 좌측으로 검색하여 두 번째 출현하는 ‘a’의 위치를 리턴한다. SQL> select instr('OracleJavaCommunity','a',-10, 2) from dual; INSTR('ORACLEJAVACOMMUNITY','A',-10,2) -------------------------------------- 8 -- 뒤에서부터 검색을 하며, 뒤에서부터 11번째부터 ‘a’를 좌측으로 검색하여 두 번째 출현하는 ‘a’의 위치를 리턴한다. SQL> select instr('OracleJavaCommunity','a',-11, 2) from dual; INSTR('ORACLEJAVACOMMUNITY','A',-11,2) -------------------------------------- 3

LENGTH(c) : 인수 c의 길이를 돌려준다.(한글도 1로 돌려줌)

LENGTHB(c) : 인수 c의 길이를 돌려준다.(바이트 단위)

SQL> select length('안녕TATATA') from dual; LENGTH('안녕TATATA') -------------------- 8 -- 아래는 lengthb 함수의 예이다, 한글이 한글자로 간주해 1을 리턴하는 length와 한글의 바이트수를 되돌리는 lengthb의 차이이다. SQL> select lengthb('안녕TATATA') from dual; LENGTHB('안녕TATATA') --------------------- 10

LOWER(c) : 인수 c를 소문자로 변환한다.

LPAD(c1, n, c2) : 전체의 길이가 n이며 c1의 왼쪽을 c2로 채운다. 만약 c2가 생략되었다면 공백으로 채운다.

LTRIM(c, [,set]) : 인수c의 왼쪽부터 c가 아닐 때까지 set에 지정된 문자를 제거한다. 만약 set이 지정되어 있지 않으면 공백을 제거한다. 흔히 LTRIM은 set을 생략하여 좌측 공백을 제거하는 용도로 많이 사용된다.

SQL> select lower('안녕TATATA') from dual; LOWER('안 ---------- 안녕tatata SQL> select ename, lpad(sal, 5, '*') from emp where rownum < 5; ENAME LPAD(SAL,5 ---------- ---------- SMITH **800 ALLEN *1600 WARD *1250 JONES *2975 -- ‘A’가 아닌 문자가 나타날 때 까지 왼쪽에서부터 제거한다. SQL> select ltrim('AAAOracleJava','A') from dual; LTRIM('AAA ---------- OracleJava -- set문자가 생략되면 공백(Blank)으로 간주한다. SQL> select ltrim(' OracleJava') from dual; LTRIM('ORA ---------- OracleJava

REPLACE(c, a, b) : 인수 c의 문자중에서 a를 b로 바꿈

RPAD(c1, n, c2) : 전체의 길이가 n이며 c1의 오른쪽을 c2로 채운다. 만약 c2가 생략되었다면 공백으로 채운다.

RTRIM(c, [,set]) : 인수c의 오른쪽부터 c가 아닐 때까지 set에 지정된 문자를 제거한다. 만약 set이 지정되어 있지 않으면 공백을 제거한다.

SQL> select replace('myJava','my','Oracle') from dual; REPLACE('M ---------- OracleJava SQL> select ename, rpad(sal,5,'*') from emp where rownum < 5; ENAME RPAD(SAL,5 ---------- ---------- SMITH 800** ALLEN 1600* WARD 1250* JONES 2975* SQL> select rtrim('JCLEEAAA','A') from dual; RTRIM ----- JCLEE -- set문자가 생략되면 공백(Blank)으로 간주한다. 주로 rtrim을 사용시 set문자를 사용하지 않고 아래의 경우처럼 공백을 제거하기 위해서도 많이 사용한다. SQL> select rtrim('JCLEEAAA ') from dual; RTRIM('J -------- JCLEEAAA

SUBSTR(c, m [,n]) : 인수 c의 m번째부터 n개 문자를 잘라낸다. n이 생략되면 c의 끝까지를 잘라낸다.

SUBSTRB(c, m [,n]) : 인수 c의 m번째부터 n개 문자를 잘라낸다. n이 생략되면 c의 끝까지를 잘라낸다.(byte단위 처리)

SQL> select substr('I LOVE KOREA', 3, 4) from dual; SUBS ---- LOVE SQL> select substr('I LOVE KOREA', 3) from dual; SUBSTR('IL ---------- LOVE KOREA -- 아래 예문은 우측에서부터 10번째 자리에서부터 문자4개를 추출한다는 의미임 SQL> select substr('I LOVE KOREA', -10, 4) from dual; SUBS ---- LOVE -- 다음 예문들은 substr과 substrb의 차이를 알 수 있는 예문이다. 두 번째 예문은 한글을 2바이트로 간주 하기(바이트 단위로 처리) 때문에 ‘안’이라는 결과가 나타나는 것이다. SQL> select substr('안녕JCLEE', 1, 2) from dual; SUBS ---- 안녕 SQL> select substrb('안녕JCLEE',1, 2) from dual; SU -- 안

TRANSLATE(c, from, to) : 인수 c의 문자열에서 from에 해당 하는 것 들을 to에 해당하는 것으로 바꾼다.

UPPER(c) : 인수 c의 문자열을 대문자로 바꾼다.

SQL> select translate('ABCDE','ABCDEFG','0123456') from dual; TRANS ----- 01234 SQL> select upper('oraclejava') from dual; UPPER('ORA ---------- ORACLEJAVA