[SQLD] 함수

/***문자형 함수***/
/* 결과: 10 */
SELECT LENGTH('SQL EXPERT') FROM DUAL;

/* 결과: 홍길동 축구선수 */
SELECT CONCAT(PLAYER_NAME, ' 축구선수') FROM PLAYER;
SELECT PLAYER_NAME||' 축구선수' FROM PLAYER;

/* SQL Server */
SELECT PLAYER_NAME+' 축구선수' FROM PLAYER;

/***숫자형 함수***/
/* 소수점 이하 한 자리까지 반올림, 내림 */
SELECT ENAME, ROUND(SAL/12, 1), TRUNC(SAL/12, 1) FROM EMP;

/*** 숫자형 함수 ***/

 

/***날짜형 함수***/
SELECT   ENAME
       , HIREDATE
       , EXTRACT (YEAR FROM HIREDATE) AS HIRE_YEAR
       , EXTRACT (MONTH FROM HIREDATE) AS HIRE_MONTH
       , EXTRACT (DAY FROM HIREDATE) AS HIRE_DAY
  FROM   EMP;
  
SELECT   ENAME
       , HIREDATE
       , TO_NUMBER(TO_CHAR(HIREDATE, 'YYYY')) AS HIRE_YEAR
       , TO_NUMBER(TO_CHAR(HIREDATE, 'MM')) AS HIRE_MONTH
       , TO_NUMBER(TO_CHAR(HIREDATE, 'DD')) AS HIRE_DAY
  FROM   EMP;

/* SQL Server */  
SELECT   ENAME
       , HIREDATE
       , DATEPART (YEAR, HIREDATE) AS HIRE_YEAR
       , DATEPART (MONTH, HIREDATE) AS HIRE_MONTH
       , DATEPART (DAY, HIREDATE) AS HIRE_DAY
  FROM   EMP; 
  
SELECT   ENAME
       , HIREDATE
       , YEAR (HIREDATE) AS HIRE_YEAR
       , MONTH (HIREDATE) AS HIRE_MONTH
       , DAY (HIREDATE) AS HIRE_DAY
  FROM   EMP;

/*** 날짜형 함수 ***/

 

/***변환형 함수***/
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD'), TO_CHAR(SYSDATE, 'YYYY. MON, DAY') FROM DUAL;

/* SQL Server */
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS CURRENTDATE

/*** 변환형 함수 ***/

 

/***CASE 표현***/
SELECT   ENAME, SAL, CASE WHEN SAL > 2000 THEN SAL ELSE 2000 END REVISED_SAL
  FROM   EMP;

/* 결과 : NVL-OK */
SELECT NVL(NULL, 'NVL-OK') AS NVL_TEST FROM DUAL;

/* 결과 : Not Null */
SELECT NVL('Not Null', 'NVL-OK') AS NVL_TEST FROM DUAL;

/* SQL Server */
SELECT ISNULL(NULL, 'NVL_OD') AS ISNULL_TEST;

/* 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다 */
SELECT COMM, SAL, COALESCE(COMM, SAL) FROM EMP;

 

/*** CASE 표현 ***/