mssql 문자함수 , 날짜함수 , 형변환, 문제

문자함수 , 날짜함수 , 형변환, 문제
 
형변환 강추

CAST(code_desc as UNSIGNED)

- BINARY
- CHAR
- DATE
- DATETIME
- SIGNED {INTEGER}
- TIME
- UNSIGNED {INTEGER}

문자함수(1)
 
select ascii('A'),ascii('B')
select ascii('a'),ascii('b') -- 문자를 해당하는 ascii 코드로
 
select char(65),char(65+32) -- 숫자를 더해서 해당하는 문자로
 
select lower('MIcRoSOFT'),upper('microSoFT') -- lower:소문자로 , upper:대문자로
 
select reverse('microsoft') -- 문자순서를 거꾸로
 
select substring('microsoft',5,3),  -- 5번째 문자부터 3개 출력
 left('microsoft',3), right('microsoft',3) -- 왼쪽에서부터 3개출력, 오른쪽에서부터 3개 출력
 
select charindex('soft','microsoft software') -- 뒷문자열에서 최초로 soft가 나오는 위치값
 
select len('microsoft'), len('마이크로 소프트') -- 문자열의 갯수(공백도 한글자)
 
select '홍'+space(20)+'길동' -- space:공백을 띄어라
 
select stuff('microsoft',3,2,'korea') -- stuff : 앞문자열에서 3번째부터 2글자를 지우고 그위치에 뒷문자열을 넣어라
 
select replace('SQL Project mssql SQLSERVER','SQL','에스큐엘') -- 대문자던 소문자던 sql이 보이면 한글 에스큐엘 로 바꿔라
 
select replicate('★',5), replicate('가',3)  -- 문자열을 숫자만큼 반복해라.
 
 

문자함수(2)
 
--emp 테이블에서 성이 '이'씨만 추출하시오.
select ename
from emp
where ename like'이%'  -- 첫글자가 '이' 뒤는 아무거나
/*
 dir a*.exe
*/
 
select ename, jik
from emp
where ename='이순신'
< DIV> 
select ename, jik
from emp
where ename like '이순신' -- like 는 %와 같이 쓰기 위함이다
 
select ename
from emp
where ename like '%이' -- 끝글자가 '이' 앞은 아무거나
 
select ename, jik
from emp
where ename like'%이%'  -- 문자열중 '이'만 나오면 출력하라
 
select ename
from emp
where ename like '_순%' -- 첫번째 글짜는 상관없고, 두번째 글자는 반드시 '순' , 뒷글자들은 상관없이...
 
select ename, jik
from emp
where ename like '김%' or ename like '이%' or ename like '홍%' -- 이씨, 김씨, 홍씨 를 출력하라
order by ename asc -- 오름차순으로 정렬 asc는 생략가능
       -- desc 는 내림차순으로 정렬
       -- order by 1 : 첫번째 컬럼을 오름차순으로 정렬
       -- order by 1 desc : 첫번째 컬럼을 내림차순으로 정렬
 
select ename '사원명', jik '직급'
from emp
where ename like '김%' or ename like '이%' or ename like '홍%' -- 이씨, 김씨, 홍씨 를 출력하라
order by '사원명'  --alias 이름 '사원명' 으로 오름차순 정렬
 
select ename
from emp
where ename like '[김이홍]%' -- 김씨, 이씨, 홍씨만 출력하라
         -- 콤마(,) 를 넣으면 (,) 도 출력 조건으로 포함되게 된다.
 
select ename
from emp
where not ename /*not*/like '[김이홍]%' -- 김씨, 이씨 ,홍씨만  빼고 출력하라
 
select ename
from emp
where ename like '[^김이홍]%'

select ename
from emp
where ename like '[^김]%' and
           ename like '[^이]%' and
           ename like '[^홍]%' 
 
 
 
select ename
from emp
where ename like '_[순미]%' -- 첫글자는 상관없고, 두번째 글자가 순 or 미 인사람만 출력하라
 
select ename, '('+jitel+')' + kuktel + '-' + tel 'TEL'
from emp
where jitel='02' and
 kuktel like '[56789]%' -- 국번이 5,6,7,8,9 로 시작하는 번호를 출력하라
         -- 숫자인데 연속적이라면 '[5-9]%'로도 할수있다.
 
wild 문자
 
create table jepum1
(name varchar(10)
,bigo varchar(500)
)
insert into jepum1 values('금반지','순도 99.99%의 순금반지')
insert into jepum1 values('은반지','고객으로 부터  99.99점수를 받')
 
select *from jepum1
select*
from jepum1
where bigo like '%99.99%%' -- wild 문자
     -- %가 문자로 받아들여지지 않고 모든것으로 해석되어 99.99가 포함된 모든 문자열이 출력된다.
 
select*
from jepum1
where bigo like '%99.99\%%' escape '\' -- \ 다음에 나오는 한글자는 탈출시켜 일반문자로 해석되게 한다.
               -- escape 다음에 어떤 문자나 숫자가 와도 되지만 , 문자로 잘 사용되지 않는 \를 사용하는것이 좋다.

예제
 
create table filetab
(fileno int
,filename varchar(50)
)
insert into filetab values(1,'c:\aaa\bbb\ccc\panmae1.xls')
insert into filetab values(2,'d:\sales.xls')
insert into filetab values(3,'e:\aaa\bbb\research.doc')
insert into filetab values(4,'c:\aaa\summer.xxls')
select *from filetab

문제1) 확장자가 '.xls'인 파일만 추출하시오.
--fileno filename
----------------------------------------------------
--1 c:\aaa\bbb\ccc\panmae1.xls
--2 d:\sales.xls
 
select fileno,filename
from filetab
where filename like '%.xls' -- 또는 where filename right(filename,4)='.xls'
order by 1
 
문제2) 아래와 같이 나오도록 추출하시오.
--fileno filename
---------------------------------------------
--1 panmae1.xls
--2 sales.xls
--3 research.doc
--4 summer.xxls

1)
select fileno,filename,
                                  reverse(left(reverse (filename),
                                                                           charindex('\',reverse(filename))-1)
                                               )
from filetab
order by 1
--?:charindex('#',reverse(filename))-1
 
2)
select fileno, filename,
right(filename,charindex('\',reverse(filename))-1)
from filetab
 
3)
select fileno, filename,
reverse(left(reverse(filename),
 charindex('\',reverse(filename))-1))
from filetab
 
4)
select fileno,right(filename,charindex('\',reverse(filename))-1)
from filetab

문제3) email컬럼에 올바른 email이 입력되도록하는 제약조건
 
create table member00
(id int
,name varchar(10)
,email varchar(50)
,constraint member_email_ck
check(
left(email,1) <> '@' and  -- 첫문자에 @가 오면 안된다.
charindex('@',
          substring(email,            -- email 문자열에서
                                  charindex('@',email)+1,          -- 최초 @가 나온 다음 문자 위치값부터
                                                                          len(email)-charindex('@',email))   -- @가 나온 이후 문자열 갯수만큼 출력
                         )=0      -- 최초 @가 나온 다음 문자열에서, @가 나오는 위치값이 0이 되게< /FONT>
`
               and
               (right(email,4) in('.com','.net','.org') or right(email,6) in ('.co.kr','.or.kr','.pe.kr'))
          )
 
)
 -- email컬럼에 올바른 email이 입력되도록하는 제약조건
jsover78@@iei.or.kr
jslover78@iei.@or.kr
jslover78@@iei.or.abc
--.com .net   .org   .kr
 
 
 
 
 
 
성이 '이' 씨인 사람의 성을 '리'씨로 변경하시오.

begin tran
update emp set ename=replace(ename,'이','리') -- ename의 '이' 모두를 '리'로 바꾼다....
where ename like '이%'
 
begin tran
update emp set ename='리' + substring(ename,2,len(ename)-1)  -- 성을 뺀 나머지 이름
where ename like '이%'
 
begin tran
update emp set ename=stuff(ename,1,1,'리') --앞문자열에서 1번째부터 1글자를 지우고 그위치에 뒷문자열을 넣어라
where ename like '이%'
 
select ename, empno
from emp
 
rollback
 
 

날짜 함수
 
 
 
datepart
 
select getdate()
select datepart(year,getdate()) -- 현재 날짜에서 년도만 출력하라

select datepart(yyyy,getdate())
select datepart(yy,getdate())   --  ASP는 반드시 y 4개, mssql에서는 2개도 된다.
 
select datepart(month,getdate())  --  현재 날짜에서 월만 출력하라
select datepart(mm,getdate())
select month(getdate())
 
select datepart(day,getdate())  --  현재 날짜에서 일만 출력하라
select datepart(dd,getdate())
select day(getdate())
 
select datepart(hour,getdate()) -- 시 출력
select datepart(hh,getdate())
 
select datepart(minute,getdate())  --  분 출력
select datepart(mi,getdate())
 
select datepart(second,getdate()) -- 초 출력
select datepart(ss,getdate())
 
select datepart(ms,getdate())  -- 밀리초 출력
 
select datepart(quarter,getdate()) -- 분기 출력
select datepart(qq,getdate())
 
select datepart(dy,getdate()) -- 2005년 1월 1일부터 몇 일째인지 출력
 
select datepart(week,getdate()) -- 2005년 1월 1일부터 몇 주째인지 출력
select datepart(wk,getdate())

select datepart(weekday,getdate()) --1:일, 2:월, 3:화 ....7:토
select datepart(dw,getdate())

select case 2 when 5-2  then '5-2=2'
                      when 5-3  then '5-3=2'
                      else '모르겠다'
                      end
 
select case  datepart(dw,getdate())
          when 1 then '일요일'
          when 2 then '월요일'
          when 3 then '화요일'
          when 4 then '수요일'
          when 5 then '목요일'
          when 6 then '금요일'
          when 7 then '토요일'
 end  '오늘의요일'                                   --select datename(dw,getdate()) 와 같다..
 
 
select ename, datepart(yy,hiredate)+'년'  --숫자타입+문자타입이므로 오류!!
from emp
           
select ename, rtrim(convert(char,datepart(yy,hiredate)))+'년 '+
                       rtrim(convert(char,datepart(mm,hiredate)))+'월 '+
                       rtrim(convert(char,datepart(dd,hiredate)))+'일 '+
                       datename(dw,hiredate) '입사일'
 from emp
 
 
 
 
 
dateadd
 
select getdate()+100
select getdate()-1
 
select dateadd(yy,2,getdate())  -- 년도에 2를 더하고
select dateadd(yy,-2,getdate()) -- 년도에 -2를 더하고
 
select dateadd(mm,2,getdate())
select dateadd(mm,-2,getdate())
 
 
 
 
 
datediff

select datediff(dd, '2005-04-01', '2005-04-11') -- 몇일 차이
select datediff(dd, '2005-04-01', getdate())
 
select datediff(hh, '2005-04-11', getdate()) -- 0시 0분 0초부터...
 
select datediff(dd, getdate(), '2005-10-04 17:50:00') -- 현재 날짜부터 뒤에 날짜까지 몇일이나 남았나...
 
 
 
 
 
D-Day
--176일 몇시간, 몇분, 몇초
 
select ltrim(str(datediff(dd, getdate(), '2005-10-04 17:50:00')))
 + '일'
 + ltrim(str(datediff(hh, getdate(), '2005-10-04 17:50:00')%24))
 +'시간'
 + ltrim(str(datediff(mi, getdate(), '2005-10-04 17:50:00')%60))
 +'분'
 + ltrim(str(datediff(ss, getdate(), '2005-10-04 17:50:00')%60))
 +'초'
 
 
 
isdate , isnumeric, nullif
 
select isdate('20050431'), isdate('20050831'), isdate('20051301')  -- 날짜 형식에 맞으면 1, 아니면 0
select isnumeric('1234'), isnumeric(1234), isnumeric('AB') -- 숫자면 1, 아니면 0
select nullif(123,123), nullif('123',123),
           nullif('abc','abc'), nullif('abc','ABC'), nullif('abc','ab') -- 앞뒤가 같으면 null값을, 다르면 앞에값을 출력한다
 
 
 
문제) 다음 테이블에서 현재나이와 성별 출력되게 하시오
 
select ename, jubun,
case substring(jubun,8,1)
 when '1' then datepart(yy,getdate()) -(left(jubun,2)+1899)    --현재년도-태어난년도 +1
 when '2' then datepart(yy,getdate()) -(left(jubun,2)+1899)
 else datepart(yy,getdate()) -(left(jubun,2)+1999)
 end '현재나이',
case  substring(jubun,8,1)
 when '1' then '남자'
 when '3' then '남자'
 else '여자'
 end '성별'
 from emp
 
 
 
 
 

문제2) 정년 퇴직일 : 한국나이로 60세가 되는해의 2월 3일로 한다.

--50세... 10년뒤의 그해의 2월 3일 (60-현재나이)
--51세... 9년뒤의 그해의 2월 3일 (60-현재나이)
 
/*
convert(char(10),
  dateadd(yy,60-(현재나이),convert(char,datepart(yy,getdate()))+'-02-03')
   ,20)
*/
 
select
convert(char(10),
  dateadd(yy,60- (case substring(jubun,8,1)
 when '1' then datepart(yy,getdate()) -(left(jubun,2)+1899)    --현재년도-태어난년도 +1
 when '2' then datepart(yy,getdate()) -(left(jubun,2)+1899)
 else datepart(yy,getdate()) -(left(jubun,2)+1999)
 end) ,
 convert(char,datepart(yy,getdate()))+'-02-03'),20
 )
from emp
 
 
 
 
 

문제3) 입사일로 부터의 근무일수
 
select datediff(dd,hiredate,getdate())
from emp
 
 
 
문제4) 정년일까지 남은 일수

select  datediff (dd,getdate(),정년일)

--hiredate '입사일',
 
 
 
 
※VIEW

create view v_정년일
as
select.......

 
 
문제5)오늘 군입대를 한다. 군 복무기간은 28개월로 한다. 제대일자를 구하시오.
         그리고 하루에 3끼를 먹는다고 가정했을때,   몇끼를 먹어야만 제대를 시켜줄까요?
 
 
select convert(char(10), dateadd(mm,26,getdate()),20) '제대일'
,datediff(dd,getdate(),dateadd(mm,26,getdate()))*3 '밥그릇수'
 
 
 
 
 
 
select문 기본 자료구조
 
 
널값의 산술
isnull(comm, 0)   -- comm의 값이 null이라면 0으로 간주하자
coalesce(sal*12+comm, sal*12, 0)  -- sal*12+comm 이 null이라면, sal*12를 주고, 둘다 null이라면 0을 줘라
 
 
형변환
convert(char, 2004) -- 숫자를 문자로
convert(datetime, '20050408')  -- 숫자를 날짜타입으로
convert(numeric,205.22) --소수점 안보이게
str(20)
 
 
주민등록번호 남자만 뽑아내기
substring(jubun,8,1) in('1','3')
 
 
문자순서를 거꾸로
reverse('microsoft')
 
 
원하는 문자열 출력하기
substring('microsoft',5,3)  -- 5번째 문자부터 3개 출력
left('microsoft',3) -- 왼쪽에서부터 3개출력
right('microsoft',3) --오른쪽에서부터 3개 출력
 

어떤 문자의 시작값 알기
charindex('soft','microsoft software') -- 뒷문자열에서 최초로 soft가 나오는 위치값
 
 
문자열 대체하기
stuff('microsoft',3,2,'korea') -- stuff : 앞문자열에서 3번째부터 2글자를 지우고 그위치에 뒷문자열을 넣어라
 

현재날짜에서 연도만 출력
datepart(year,getdate())
 
 
현재날짜에 더하기
dateadd(yy,2,getdate())  -- 년도에 2를 더하고
 
 
날짜의 차이값
datediff(dd, getdate(), '2005-10-04 17:50:00') -- 현재 날짜부터 뒤에 날짜까지 몇일이나 남았나...
 
 
국제표준시간 (년,월,일 까지만)
convert(char(10), getdate(), 20) 
 
 

case문
case  substring(jubun,8,1)
 when '1' then '남자'
 when '3' then '남자'
 else '여자'
 end