문자함수(1)
select ascii('A'),ascii('B')
select ascii('a'),ascii('b') -- 문자를 해당하는 ascii 코드로
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개 출력
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'이%' -- 첫글자가 '이' 뒤는 아무거나
from emp
where ename like'이%' -- 첫글자가 '이' 뒤는 아무거나
/*
dir a*.exe
dir a*.exe
*/
select ename, jik
from emp
where ename='이순신'
< DIV> from emp
where ename='이순신'
select ename, jik
from emp
where ename like '이순신' -- like 는 %와 같이 쓰기 위함이다
from emp
where ename like '이순신' -- like 는 %와 같이 쓰기 위함이다
select ename
from emp
where ename like '%이' -- 끝글자가 '이' 앞은 아무거나
from emp
where ename like '%이' -- 끝글자가 '이' 앞은 아무거나
select ename, jik
from emp
where ename like'%이%' -- 문자열중 '이'만 나오면 출력하라
from emp
where ename like'%이%' -- 문자열중 '이'만 나오면 출력하라
select ename
from emp
where ename like '_순%' -- 첫번째 글짜는 상관없고, 두번째 글자는 반드시 '순' , 뒷글자들은 상관없이...
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 : 첫번째 컬럼을 내림차순으로 정렬
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 이름 '사원명' 으로 오름차순 정렬
from emp
where ename like '김%' or ename like '이%' or ename like '홍%' -- 이씨, 김씨, 홍씨 를 출력하라
order by '사원명' --alias 이름 '사원명' 으로 오름차순 정렬
select ename
from emp
where ename like '[김이홍]%' -- 김씨, 이씨, 홍씨만 출력하라
-- 콤마(,) 를 넣으면 (,) 도 출력 조건으로 포함되게 된다.
from emp
where ename like '[김이홍]%' -- 김씨, 이씨, 홍씨만 출력하라
-- 콤마(,) 를 넣으면 (,) 도 출력 조건으로 포함되게 된다.
select ename
from emp
where not ename /*not*/like '[김이홍]%' -- 김씨, 이씨 ,홍씨만 빼고 출력하라
from emp
where not ename /*not*/like '[김이홍]%' -- 김씨, 이씨 ,홍씨만 빼고 출력하라
select ename
from emp
where ename like '[^김이홍]%'
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 미 인사람만 출력하라
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]%'로도 할수있다.
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)
)
(name varchar(10)
,bigo varchar(500)
)
insert into jepum1 values('금반지','순도 99.99%의 순금반지')
insert into jepum1 values('은반지','고객으로 부터 99.99점수를 받')
select *from jepum1
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 다음에 어떤 문자나 숫자가 와도 되지만 , 문자로 잘 사용되지 않는 \를 사용하는것이 좋다.
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)
)
(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')
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
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 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
from filetab
order by 1
--?:charindex('#',reverse(filename))-1
2)
select fileno, filename,
right(filename,charindex('\',reverse(filename))-1)
from filetab
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
select fileno, filename,
reverse(left(reverse(filename),
charindex('\',reverse(filename))-1))
from filetab
4)
select fileno,right(filename,charindex('\',reverse(filename))-1)
from filetab
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>
(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이 입력되도록하는 제약조건
and
(right(email,4) in('.com','.net','.org') or right(email,6) in ('.co.kr','.or.kr','.pe.kr'))
)
)
-- email컬럼에 올바른 email이 입력되도록하는 제약조건
--.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 '이%'
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 '이%'
update emp set ename=stuff(ename,1,1,'리') --앞문자열에서 1번째부터 1글자를 지우고 그위치에 뒷문자열을 넣어라
where ename like '이%'
select ename, empno
from emp
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(mm,getdate())
select month(getdate())
select datepart(day,getdate()) -- 현재 날짜에서 일만 출력하라
select datepart(dd,getdate())
select day(getdate())
select datepart(dd,getdate())
select day(getdate())
select datepart(hour,getdate()) -- 시 출력
select datepart(hh,getdate())
select datepart(hh,getdate())
select datepart(minute,getdate()) -- 분 출력
select datepart(mi,getdate())
select datepart(mi,getdate())
select datepart(second,getdate()) -- 초 출력
select datepart(ss,getdate())
select datepart(ss,getdate())
select datepart(ms,getdate()) -- 밀리초 출력
select datepart(quarter,getdate()) -- 분기 출력
select datepart(qq,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()) 와 같다..
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
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 getdate()-1
select dateadd(yy,2,getdate()) -- 년도에 2를 더하고
select dateadd(yy,-2,getdate()) -- 년도에 -2를 더하고
select dateadd(yy,-2,getdate()) -- 년도에 -2를 더하고
select dateadd(mm,2,getdate())
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))
+'초'
--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값을, 다르면 앞에값을 출력한다
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 '성별'
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)
*/
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
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
from emp
문제4) 정년일까지 남은 일수
select datediff (dd,getdate(),정년일)
--hiredate '입사일',
※VIEW
create view v_정년일
as
select.......
create view v_정년일
as
select.......
문제5)오늘 군입대를 한다. 군 복무기간은 28개월로 한다. 제대일자를 구하시오.
그리고 하루에 3끼를 먹는다고 가정했을때, 몇끼를 먹어야만 제대를 시켜줄까요?
그리고 하루에 3끼를 먹는다고 가정했을때, 몇끼를 먹어야만 제대를 시켜줄까요?
select convert(char(10), dateadd(mm,26,getdate()),20) '제대일'
,datediff(dd,getdate(),dateadd(mm,26,getdate()))*3 '밥그릇수'
,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을 줘라
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)
convert(char, 2004) -- 숫자를 문자로
convert(datetime, '20050408') -- 숫자를 날짜타입으로
convert(numeric,205.22) --소수점 안보이게
str(20)
주민등록번호 남자만 뽑아내기
substring(jubun,8,1) in('1','3')
substring(jubun,8,1) in('1','3')
문자순서를 거꾸로
reverse('microsoft')
reverse('microsoft')
원하는 문자열 출력하기
substring('microsoft',5,3) -- 5번째 문자부터 3개 출력
left('microsoft',3) -- 왼쪽에서부터 3개출력
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글자를 지우고 그위치에 뒷문자열을 넣어라
stuff('microsoft',3,2,'korea') -- stuff : 앞문자열에서 3번째부터 2글자를 지우고 그위치에 뒷문자열을 넣어라
현재날짜에서 연도만 출력
datepart(year,getdate())
현재날짜에 더하기
dateadd(yy,2,getdate()) -- 년도에 2를 더하고
dateadd(yy,2,getdate()) -- 년도에 2를 더하고
날짜의 차이값
datediff(dd, getdate(), '2005-10-04 17:50:00') -- 현재 날짜부터 뒤에 날짜까지 몇일이나 남았나...
datediff(dd, getdate(), '2005-10-04 17:50:00') -- 현재 날짜부터 뒤에 날짜까지 몇일이나 남았나...
국제표준시간 (년,월,일 까지만)
convert(char(10), getdate(), 20)
convert(char(10), getdate(), 20)
case문
case substring(jubun,8,1)
when '1' then '남자'
when '3' then '남자'
else '여자'
end
'Programming > DataBase' 카테고리의 다른 글
[Mysql] sqlgate mysql 키보드 이상 현상 (0) | 2013.07.12 |
---|---|
[MSSQL] Cannot insert explicit value for identity column in table (0) | 2013.02.26 |
[mssql] left outer join (0) | 2012.12.13 |
[oracle] 계층 쿼리시 계층별로 정렬하기 위해.. order siblings by (0) | 2012.11.20 |
[oracle]START WITH ... CONNECT BY 절을 사용한 계층형쿼리 (0) | 2012.11.20 |