2012.01.03 SQL 2일차(1) 문자함수(LPAD,RPAD,REPLACE), 숫자함수, 날짜함수, 변환함수

문자함수

LPAD('대상문자열', 지정한 길이, '추가할문자열')

; 대상 문자열이 사용자가 지정한 길이보다 작으면, 사용자가 지정한 문자열을 대상문자열에 추가하는 키워드

(LPAD는 왼쪽에 추가)

RPAD('대상문자열', 지정한 길이, '추가할문자열')

; 대상 문자열이 사용자가 지정한 길이보다 작으면, 사용자가 지정한 문자열을 대상문자열에 추가하는 키워드

(RPAD는 오른쪽에 추가)

예제

SQL> select lpad(dept_name,10,'1234567890')   2  from tdept; LPAD(DEPT_NAME,10,'1 -------------------- 12경영지원 123456재무 .... 12345영업1 12345영업2 10 rows selected. ============================ SQL> select lpad(dept_name,10,'*')   2  from tdept; LPAD(DEPT_NAME,10,'* -------------------- **경영지원 .... *****영업1 *****영업2 10 rows selected.

문제 : TDEPT 테이블의 DEPT_NAME 컬럼을 아래 예시처럼 출력 되게 쿼리를 작성하세요 경영지원90 재무567890 총무567890 기술지원90 H/W지원890          ----->           S/W지원890 영업567890 영업기획90 영업167890 영업267890 10 rows selected

1  select rpad(dept_name,10,   2  substr('1234567890',  lengthb(dept_name)+1,10) )   3* from tdept SQL> / RPAD(DEPT_NAME,10,SU -------------------- 경영지원90 재무567890 ..... 영업167890 영업267890 10 rows selected. (같은 결과   1 select dept_name||   2  substr('1234567890',  lengthb(dept_name)+1,10)   3  from tdept;

문자함수 

REPLACE( 대상col(또는 문자열), 문자열1, 문자열2 )

문자열1을 문자열2로 바꾼다.

student 테이블에서 tel 컬럼의 국번을 ###가 되도록 출력하세요.
(국번은 모두 3자리라고 가정)   1  select tel, replace(tel,   2  substr(tel, instr(tel, ')',1,1)+1, 3),   3  '###') "당첨자"   4* from student SQL> / TEL           당첨자 ------------- --------------------------------------- 051)781-2158  051)###-2158 055)426-1752  055)###-1752 .... 052)175-3941  052)###-3941 02)785-6984   02)###-6984 055)248-3679  055)###-3679 16 rows selected.	(국번은 모두 3자리라고 가정하지 않음)    1  select tel, replace(tel,   2  substr(tel, instr(tel, ')',1,1)+1,   3     instr(tel,'-',1,1)-1 -instr(tel,')',1,1) ),   4  '###') "당첨자"   5* from student SQL> / TEL           당첨자 ------------- --------------------------------------- 051)781-2158  051)###-2158 055)426-1752  055)###-1752 .... 02)785-6984   02)###-6984 055)248-3679  055)###-3679 16 rows selected.

숫자함수( 항상 자릿수 조심 )

ROUND : 지정한 자리이하에서 반올림

ROUND(123.572, 1)   ----->   123.6

ROUND(3719.2917, -3)  ----->  4000

TRUNC : 지정한 자리이하에서 내림

TRUNC(123.572, 1)  ----->  123.5

TRUNC(3719.2917, -3)  ----->  3000

MOD : m을 n으로 나눈 나머지 값

MOD(12, 10)  --->  2

CEIL : 소수부분에서 올림

CEIL(182.125)  --->  183

FLOOR : 소수부분삭제

FLOOR(182.928)  --->   182

날짜함수

SYSDATE : 현재날짜

MONTHS_BETWEEN(DATE1, DATE2)

DATE1, DATE2 사이의 개월 수

* 주의사항 : 1월1일과 7월1일의 결과값과, 1월31일과 7월1일의 결과값이 같으므로 조심해서 씁니다.

(단순히 개월의 차이만 출력합니다. 일계산하지 않음)

ADD_MONTHS(DATE, 개월수) : DATE + 개월수 해서 출력

NEXT_DAY(DATE, '요일') : DATE 이후에 다가오는 처음'요일'의 날 출력

LAST_DAY(DATE) : DATE가 속한 달의 마지막 날을 출력

ROUND(DATE) : DATE가 정오를 넘으면 다음날 출력

TRUNC(DATE) : DATE 당일 출력

1  select sysdate "현재날짜", add_months(sysdate,4) "4개월뒤",   2  next_day(sysdate, 'sun') "다음일요일" , last_day(sysdate) "마지막날",   3* round(sysdate) round, trunc(sysdate) trunc from dual SQL> / 현재날짜        4개월뒤         다음일요일     마지막날       ROUND        TRUNC ------------ ------------ ------------ ------------ ------------ ------------ 04-JAN-12    04-MAY-12    08-JAN-12      31-JAN-12    05-JAN-12    04-JAN-12

1  select months_between(sysdate, hiredate) "함수사용",   2  (sysdate-hiredate)/30 "직접계산"   3* from professor SQL> /   함수사용   직접계산 ---------- ---------- 354.376458 359.555673 200.763554  203.72234 ...... 279.731296 283.789006 127.118393 128.989006 253.570006 257.255673 8 rows selected. MONTHS_BETWEEN 과 일수직접계산과는 결과값이 다릅니다.

변환함수(데이터타입을 변환)

TO_CHAR : 숫자, 날짜를 문자타입으로 변환

TO_NUMBER : 문자열을 숫자로 변환(ASCII코드는 생각하지 않고, 숫자같은 문자열만 변환한다.)

TO_DATE : 문자열을 날짜타입으로 변환

TO_CHAR 설명

사용법 : TO_CHAR( date or number, 'format' )

'format' 부분설명

1. 날짜를 문자로 바꿀때의 형식(일부 형식은 대소문자에 따라 결과값이 대문자 OR 소문자로 출력됩니다.)

년도	YYYY    결과 : 2012	YY    결과 : 12	YEAR(year) 결과 : TWENTY TWELVE(twenty twelve)
	RRRR  (YYYY와 동일)	RR  (YY와 동일)	10g 부터는 R이 권장됨
월	MM    결과 : 01	MON(mon)    결과 : JAN(jan)	MONTH(month)    결과 : JANUARY(january)
일	DD    결과 : 04
요일	DAY(day)    결과 : SAT(sat)
시간	HH      결과 : 04	HH24     결과 : 16
분	MI      결과 : 36
초	SS      결과 : 26

2. 숫자를 문자로 바꿀때의 형식

종류	의 미	사용 예	결과
9	한자리의 숫자 표시	TO_CHAR( 1234, '99999' )	1234
0	앞 부분을 0으로 표시	TO_CHAR( 1234, '09999' )	01234
$	$를 앞에 표시	TO_CHAR( 1234, '$99999 )	$1234
.	소수점 표시	TO_CHAR( 1234, '99999.999' )	1234.000
,	특정 위치에 ',' 표시	TO_CHAR( 12345, '99,999' )	12,345
MI	음수값일 경우 오른쪽에 - 표시	TO_CHAR( -1234, '9999MI' )	1234-
PR	음수값을 <>로 표시	TO_CHAR( -1234, '9999PR' )	<1234>
EEEE	과학적 표기법(E사용)으로 표시	TO_CHAR( 12345, '9.999EEEE')	1.235E+04
V	10n을 곱한값으로 표시	TO_CHAR( 1234, '99999v9999' )	12340000
B	공백을 0으로 표시	TO_CHAR( 1234, 'B9999.99' )	1234.00

TO_CHAR 예제

날짜를 문자로 변경   1  select sysdate,   2  to_char(sysdate, 'rrrr-MON-DD-hh24:mi:ss') now,   3  to_char(sysdate, 'rr-mon-dd-hh24:mi:ss:DAY') now2   4* from dual SQL> / SYSDATE      NOW                     NOW2 ------------ ----------------------- ----------------------------------- 04-JAN-12    2012-JAN-04-17:01:09    12-jan-04-17:01:09:WEDNESDAY

숫자를 문자로 변경   1  select '12345' "초기",   2  to_char(12345, '0999999') "0사용",   3  to_char(12345, '$99999') "$표기",   4  to_char(-12345, '99999pr') "음수<>표기"   5* from dual SQL> / 초기  0사용    $표기   음수<> ----- -------- ------- ------- 12345  0012345  $12345 <12345>

실습문제 : 학생테이블의 birthdate 컬럼을 이용하여 생일이 5월인 사람만 이름,생일 을 출력하세요.   1  select name, to_char(birthdate,'mm/dd') "생일"   2  from student   3* where to_char(birthdate,'mm') = '05' SQL> / NAME       생일 ---------- ----- 박미경     05/16