:: 작업장

 종류

내용 

함수의 예 

 문자형 함수

문자를 입력하면 문자나 숫자 값을 반환한다. 

LOWER, UPPER, 

SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII 

숫자형 함수

 숫자를 입력하면 숫자 값을 반환한다

ABS, MOD, ROUND, SIGN, 

CHR/CHAR, CEIL/CEILING,

FLOOR, EXP,LOG, LN, POWER, SIN, COS, TAN 

날짜형 함수

DATE 타입의 값을 연산한다. 

SYSDATE/GENDATE,

EXTRACT/DATEPART, 

TO_NUMBER(TOCHAR(d.'yyyy' | 'MM'|'DD')) /

YEAR | MONTH | DAY

변환형 함수

문자, 숫자, 날짜형 값의 데이터 타입을 변환한다. 

TO_NUMBER, TO_CHAR,

TO_DATE / CAST,

CONVERT 

NULL 관련 함수

NULL을 처리하기 위한 함수  

NVL/ISNULL,

NULLIF,

COALESCE 

문자형 함수

함수 설명 

LOWER(문자형)

문자열의 알파벳 문자를 소문자로 바꿔준다. 

UPPER(문자형)

문자열의 알파벳 문자를 대문자로 바꿔준다. 

ASCII(문자)

문자나 숫자를 ASCII코드 번호로 바꿔준다. 

CHR/CHAR(ASCII 번호)

ASCII 코드 번호를 문자로 바꿔준다. 

 CONCAT(문자열1, 문자열2)

문자열1과 문자열2를 연결한다

- 오라클의 ||

- SQL server의 + 와 같은 역할 

 SUBSTR/ SUBSTRING

(문자열, m [,n])

문자열 중 m위치에서 n 개의 문자길이에 해당하는 문자를 돌려준다.(n이 생략될 경우 마지막 문자까지) 

 LENGTH /LEN(문자열)

문자열의 개수를 숫자값으로 돌려준다. 

 LTRIM

(문자열 [, 지정문자])

문자열의 첫 문자부터 확인해서 지정문자가 나타나면 해당 문자를 제거한다.

SQL server경우 LTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. 

 RTRIM

(문자열 [,지정문자])

문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다.

SQL server경우 RTRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다. 

TRIM

([leading | trailing | both 지정문자 FROM 문자열)

문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거한다.

(leading | trailing | both가 생략되면 both가 디폴트)

SQL server경우 TRIM함수에 지정문자를 사용할 수 없다. 즉 공백만 제거한다.  

 LOWER('SQL Expert')

sql expert 

 UPPER('SQL Expert')

SQL EXPERT 

 ASCII('A')

65 

 CHR(65) / CHAR(65)

A 

CONCAT('RDBMS','SQL')

'RDBMS' || 'SQL' / 
'RDBMS' +' SQL'

RDBMSSQL 

SUBSTR('SQL Expert',5,3)

/ SUBSTRING('SQL Expert',5,3)

 Exp

SUBSTR('SQL Expert',5)

 Expert ( 맨끝까지 출력)

LENGTH('SQL Expert') / 

LEN('SQL Expert')

 10 (공백도 길이로 간주)

LTRIM('xxxYYYZZxYZ','x')

 YYYZZxYZ 

(왼쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.)

RTRIM('XXYYzzXYzz','z')

XXYYzzXY

(오른쪽부터 x를 지우며 x와 다른값을 만나는 이후 문자열에 대해서는 전체다 출력.)

RTRIM('XXYYzzXYzz      ')

XXYYzzXYzz

* 공백 제거 결과출력

공백제거 및 CHAR와 VARCHAR 데이터 유형을 비교할 때 용이하게 사용됨.

 TRIM('x' FROM 'xxYYZZxYZxx')

YYZZxYZ 

TRIM(leading 'x' FROM 'xxYYZZxYZxx')

YYZZxYZxx

(LTRIM과 같은결과)

TRIM(trailing 'x' FROM 'xxYYZZxYZxx')

xxYYZZxYZ

(RTRIM과 같은결과)

 숫자형 함수

함수설명 

 ABS(숫자)

숫자의 절대값 리턴 

 SIGN(숫자)

숫자가 양수인지 음수인지 0 인지 구분 

 MOD(숫자1,숫자2)

숫자1을 숫자2로 나눈 값의 나머지 리턴

% 연선자로 대체가능 (7%3) 

 CEIL/CEILING(숫자)

숫자보다 크거나 같은 최소 정수를 리턴한다. 

 FLOOR(숫자)

숫자보다 작거나 같은 최대 정수를 리턴한다. 

 ROUND(숫자 [, m])

숫자를 소수점 m+1자리에서 반올림하여 리턴.

m이 생략되면 디폴트 값은 0 

 TRUNCATE(숫자 [,m])

숫자를 소수 m+1 자리에서 버린 후 리턴

m 이 생략되면 디폴트 값은 0

SQL server에서는 제공하지 않는다. 

 SIN, COS, TAN

삼각함수 값을 리턴 

EXP()

POWER()

SQRT()

LOG()

LN()

숫자의 지수 리턴

거듭제곱 리턴

제곱근 리턴

자연로그값 리턴 

 숫자형 함수 사용

결과 값 및 설명 

 ABS(-15)

15 

 SIGN(-20)

-1 

 SIGN(0)

0 

 SIGN(20)

1 

 MOD(7/3)

1 

 7%3

1 

 CEIL(38.123) / CEILING(38.123)

39  

 CEIL(38) / CEILING(38)

38  (같으면 같은값) 

 CEIL(-38.123) / CEILING(- 38.123)

-38 ( -38이 -38.123 보다 크므로)

 FLOOR(38,123)

38 

 FLOOR(-38.123)

-39 (-39가 -38.123 보다 작으므로) 

 FLOOR(-38) 

-38 (같으면 같은값)

 ROUND(38.5235,3)

38.524 ( 3째 자리까지 표현 4째자리 반올림) 

 ROUND(38.5235,1)

38.5 (소수 첫재짜리 까지 표현 둘째자리 반올림)

 ROUND(38.5235,0)

39 

 ROUND(38.5235)

39 (인수가 없을 경우 0이 DEFAULT) 

 TRUNC(38.5235,3)

38.523 

 TRUNC(38.5235,1)

38.5 

 TRUNC(38.5235,0)

38 

 TRUNC(38.5235)

38(인수가 없을 경우 0이 DEFAULT) 

 날짜형 함수

함수 설명 

 SYSDATE / GETDATE()

 현재 날짜와 시각을 출력한다.

 EXTRACT('YEAR' | 'MONTH' | DAY' from d)

/ DATEPART('YEAR' | 'MONTH' | DAY ,d)

날짜 데이터에 년/월/일 데이터를 출력할 수 있다.

시간 / 분 /초도 가능함 

 TO_NUMBER(TO_CHAR(d,'YYYY')

/ YEAR(d),

 TO_NUMBER(TO_CHAR(d,'MM')

/ MONTH(d),

 TO_NUMBER(TO_CHAR(d,'DD')

/ DAY(d)

날짜 데이터에서 년/월/일 데이터를 출력할 수 있다.

ORACLE EXTRACT YEAR/MONTH/DAY옵션이나

SQL SERVER DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다.

TO_NUMBER 함수 제외시 문자로 출력. 

 연산

결과 

설명 

 날짜 + 숫자

날짜 

숫자만큼의 날수를 날짜에 더한다 

 날짜 - 숫자 

날짜 

숫자만큼의 날수를 날짜에 뺀다 

 날짜1 - 날짜2 

날짜 수 

다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다 

 날짜 + 숫자/24

날짜 

시간을 날짜에 더한다 

 종류

 설명

 명시적 데이터 유형 변환

데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우 

 암시적 데이터 유형 변환

데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우 

 변환형 함수- ORACLE

함수설명 

 TO_NUMBER(문자열)

alhanumeric 문자열을 숫자로 변환한다. 

 TO_CHAR(숫자 | 날짜 [, FORMAT])

숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다 

 TO_DATE(문자열 [, FORMAT])

문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다. 

변환형 함수 -  SQL server 

함수 설명 

 CAST (expression AS data_type [(length)])

expression을 목표 데이터 유형으로 변환한다. 

 CONVERT (data_type [(length)], expression [,style])

expression을 목표 데이터 유형으로 변환한다. 

 SCOTT@PROD>select sysdate from dual;

SYSDATE

---------

28-AUG-15

SCOTT@PROD>select TO_CHAR(SYSDATE,'YYYY/MM/DD')

  2  FROM DUAL;

TO_CHAR(SY

----------

2015/08/28

SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY,MON,DAY') 

  2  FROM DUAL;

TO_CHAR(SYSDATE,'YYYY,MON,DAY')

------------------------------------------------------

2015,AUG,FRIDAY

SCOTT@PROD>SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD') FROM DUAL;

TO_CHAR(SY

----------

2015.08.28

SELECT CONVERT(VARCHAR(10)M GETDATE(),11) AS CURRENTDATE

CURRENT DATE

________________

2015/08/28

SCOTT@PROD>SELECT TO_CHAR(123456789/1200,'$999,999,999.99')

  2  FROM DUAL;

TO_CHAR(12345678

----------------

     $102,880.66

SELECT TO_CHAR(123456789/1200,'$9999999.99') FROM DUAL;

TO_CHAR(1234

------------

  $102880.66

* 원하는 형식을 9와 , 로 자를 수 있다.

SELECT TO_CHAR(123456789/1200,'L9999999.99') FROM DUAL;

TO_CHAR(1234

------------

  ￦102,880.66

* L을 쓰면 로컬 화폐 단위로 출력한다.

 CASE 표현

함수설명 

 CASE

    SIMPLE_CASE_EXPRESSION 조건

    ELSE  표현절

END

SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건 내의 THEN 절을 수행하고 조건이 맞지 않는 경우 ELSE 절을 수행한다. 

 CASE

  SEARCHED_CASE_EXPRESSION 조건

  ELSE   표현절

END

SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의 THEN 절을 수행하고 조건이 맞지 않는경우 ELSE 절을 수행한다 

 DECODE( 표현식, 기준값1, 값1 

[, 기준값2,값2,...., 디폴드값])

ORACLE에서만 사용되는 함수로 표현식의 값이 기준값1이면 값1을 출력하고 기준값2임ㄴ 값2를 출력한다. 그리고기준값이 없으면 디폴트 값을 출력한다. CASE 표현의 SIMPLE_CASE_EXPRESSION 조건절과 동일하다. 

SELECT LOC

 CASE LOC

            WHEN  'NEW YORK' THEN 'EAST'

            WHEN  'BOSTON' THEN 'EAST'

            ELSE 'ETC'

            END as AREA

FROM DEPT;

LOC             AREA

---------------- ----------

NEW YORK EAST

BOSTON     EAST

SELECT ENAME,

               CASE WHEN SAL >=3000 THEN 'HIGH'

                          WHEN SAL >=1000 THEN 'MID'

                          ELSE 'LOW'

               END AS SALARY_GRADE

FROM EMP;

ENAME   SALARY_GRADE

---------- ----

GOODBOY    LOW

ALLEN   MID

WARD   MID

JONES   MID

MARTIN   MID

BLAKE   MID

CLARK   MID

SCOTT   HIGH

KING   HIGH

TURNER   MID

ADAMS   MID

JAMES   LOW

FORD   HIGH

MILLER   MID           

 연산

연산의 결과 

NULL + 2, 2 + NULL

NULL 

NULL - 2, 2 - NULL

NULL 

NULL * 2, 2 * NULL

NULL

NULL / 2, 2 / NULL

NULL 

 일반형 함수

함수설명 

 NVL(표현식1, 표현식2)

/ ISNULL(표현식1, 표현식2)

표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다.

단, 표현식과 표현식2의 결과 데이터 값이 같아야 한다.

NULL관련 가장 많이 사용되는 함수이므로 중요함. 

NULLIF(표현식1, 표현식2)

표현식1이 표현식2와 같으면 NULL,

같지않으면 표현식 1을 리턴한다. 

COALESCE(표현식1,표현식2,....)

임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.