출처 : http://www.sqler.com/269782
1. 문자 식에서 가장 왼쪽 문자의 ASCII 코드 값 반환 (숫자로 표시됨)
|
구분 |
MSSQL |
Oracle |
|
함수 |
ASCII |
ASCII |
|
용례 |
SELECT ASCII('A') |
SELECT ASCII('A') FROM DUAL |
|
결과 |
65 |
65 |
è MSSQL이든 ORacle이든 ‘’ 안에 여러 개의 문자가 있어도 가장 좌측 값만 반환합니다.
즉, SELECT ASCII(‘A’) 나 SELECT ASCII(‘ABCDEFG’) 나 결과는 같습니다.
2. 문자 합치기
|
구분 |
MSSQL |
Oracle |
|
함수 |
+ |
CONCAT 또는 || |
|
용례 |
SELECT '동해물과' + '백두산이' |
1. SELECT '동해물과 ' || '백두산이' FROM DUAL
2. SELECT CONCAT('동해물과 ','백두산이') FROM DUAL |
|
결과 |
동해물과 백두산이 |
동해물과 백두산이 |
3. ASCII 코드를 문자로 변환하기
|
구분 |
MSSQL |
Oracle |
|
함수 |
CHAR |
CHR |
|
용례 |
SELECT CHAR(67) |
SELECT CHR(67) FROM DUAL |
|
결과 |
C |
C |
è 참고로 9번은 TAB, 10은 LF(Line Feed), 13번은 CR(Carriage Return) 입니다.
4. 좌측에서 몇 번째에 해당 문자가 있는지 알려주기
|
구분 |
MSSQL |
Oracle |
|
함수 |
CHARINDEX |
INSTR |
|
용례 |
SELECT CHARINDEX('마이', '고마해라. 마이 무우따 아이가?') |
SELECT INSTR('고마해라. 마이 무우따 아이가?','마이' ) FROM DUAL |
|
결과 |
7 |
7 |
è MSSQL과 Oracle의 함수사용 순서가 다릅니다.
è MSSQL에는 패턴찾기에 PATINDEX를 많이 사용합니다. 위와 같은 결과를 얻으려면
SELECT PATINDEX('%마이%', '고마해라. 마이 무우따 아이가?')
처럼 사용하면 됩니다.(와일드 카드 사용 가능)
5. 대문자 변환, 소문자 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
UPPER / LOWER |
UPPER / LOWER |
|
용례 |
SELECT UPPER('aBcDeF'), LOWER('aBcDeF') |
SELECT UPPER('aBcDeF'), LOWER('aBcDeF') FROM DUAL |
|
결과 |
ABCDEF abcdef |
ABCDEF abcdef |
6. 좌측공간을 특정 문자로 채워주기
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음(없음) |
LPAD |
|
용례 |
- |
SELECT LPAD('13579',10, '0') FROM DUAL |
|
결과 |
- |
0000013579 |
è MSSQL에선 없는 함수이기 때문에 아래와 같이 사용자함수를 만들어서 사용하기도 합니다.
|
-- 1. FUNCTION 만들기
CREATE FUNCTION dbo.UFN_LPAD
(
@INPUT VARCHAR(8000),
@COUNT AS INT,
@FILLCHAR AS CHAR(1)=' '
)
RETURNS varchar(200)
AS
BEGIN
RETURN
CASE
WHEN LEN(@INPUT) >= @COUNT THEN LEFT(@INPUT, @COUNT)
ELSE
LEFT(REPLICATE(@FILLCHAR, @COUNT), @COUNT-LEN(@INPUT)) + @INPUT
END
END
-- 2. SAMPLE
SELECT dbo.UFN_LPAD('12',10,'0') AS PR_KEY
--> 결과
0000000012
|
7. 우측공간을 특정 문자로 채워주기
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지않음(없음) |
RPAD |
|
용례 |
- |
SELECT RPAD('13579',10, '0') FROM DUAL |
|
결과 |
- |
1357900000 |
è MSSQL에선 없는 함수이기 때문에 UFN_LPAD처럼 함수를 만들어서 씁니다.
다만, REPLICATE라는 함수가 있는데, 이것은 특정문자를 연속적으로 채워 줄 뿐, RPAD와는 조금 다릅니다.
|
-- 사용례
SELECT REPLICATE('0',10)
--> 결과
0000000000 |
또한, SPACE라는 함수는 공백만 채워줍니다.
|
-- 사용례
SELECT '나의' + SPACE(10) + '것'
--> 결과
나의 것 |
8. 좌/우 공백 없애주기
|
구분 |
MSSQL |
Oracle |
|
함수 |
LTRIM / RTRIM |
LTRIM / RTRIM |
|
용례 |
SELECT LTRIM(' 아버지'), RTRIM('어머니 ') |
SELECT LTRIM(' 아버지'), RTRIM('어머니 ') FROM DUAL |
|
결과 |
아버지 어머니 à (공백제거됨) |
아버지 어머니 à (공백제거됨) |
9. 문자의 음성표현을 가지는 문자열을 반환. 국내에서는 흔히 사용하지 않음(한글 동작 안함)
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
l = 4
m, n = 5
r = 6
|
구분 |
MSSQL |
Oracle |
|
함수 |
SOUNDEX |
SOUNDEX |
|
용례 |
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe'); |
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') FROM DUAL |
|
결과 |
S252 S200 |
S530 S530 |
è 어라? 예제에는 같은 것으로 되어 있는데, 실제 결과는 다르네요. -_- 이것 참…
10. 전체 문자열에서 지정한 길이만큼의 문자열 반환
|
구분 |
MSSQL |
Oracle |
|
함수 |
SUBSTRING |
SUBSTR |
|
용례 |
SELECT SUBSTRING('1234567890',4,5) |
SELECT SUBSTR('1234567890',4,5) FROM DUAL |
|
결과 |
45678 |
45678 |
è 4번째 자리에서 시작해서 다섯 글자를 가져옵니다.
11. 문자열 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
REPLACE |
REPLACE |
|
용례 |
SELECT REPLACE('1234567','123','321') |
SELECT REPLACE('1234567','123','321') FROM DUAL |
|
결과 |
3214567 |
3214567 |
è MSSQL의 STUFF라는 함수가 있는데요. 이것은 엑셀의 REPLACE와 같습니다.
|
SELECT STUFF('13579',2,3,'222')
--> 결과
12229 |
12. 음절의 첫 글자만 대문자로 변환(Pascal Case)
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
INITCAP |
|
용례 |
- |
SELECT INITCAP('we are the world') FROM DUAL; |
|
결과 |
- |
We Are The World |
11. 문자열에서 특정 문자만 쏙쏙 골라서 다른 문자로 변환, 정확하게 이야기하면 지정한 문자와 맞으면 특정 문자로 처리함, 또는 특정 지정문자가 없는 경우 해당 문자를 삭제함.
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
TRANSLATE |
|
용례 |
- |
SELECT TRANSLATE('NothingToUse','o','!') FROM DUAL;
SELECT TRANSLATE('BFG123', '12345BCDEFG', '123XXXXXXXX') FROM DUAL; |
|
결과 |
- |
N!thingT!Use
XXX123 |
è 아래 SAMPLE을 참조하세요.
|
create table translate_test
(
mem_id varchar(20)
)
;
INSERT INTO translate_test (mem_id) values ('ABCD1234');
INSERT INTO translate_test (mem_id) values ('abcdef');
INSERT INTO translate_test (mem_id) values ('585472');
COMMIT;
select mem_id, translate(mem_id, '0123456789' || mem_id, '0123456789')
from translate_test;
-- 결과
ABCD1234 1234
abcdef
585472 585472 |
14. 나열한 인수 중 가장 큰 값 / 작은 값을 반환
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
GREATEST / LEAST |
|
용례 |
- |
SELECT GREATEST('Z9', '나의','콜','A341', '999','123') FROM DUAL;
SELECT LEAST('Z9', '나의','콜','A341', '999','123') FROM DUAL; |
|
결과 |
- |
콜 / 123 |
è 비교하는 것은, 문자순위(A보다는 Z가 크다. 한글은 자모순서에 의함), 숫자순위(0보다는 9가 크가), 글자수단위(같은 문자로 시작하더라도 결국 글자수가 많은 것이 크다.)
15. 길이 가져오기, 또는 BYTE 단위 길이 가져오기
|
구분 |
MSSQL |
Oracle |
|
함수 |
LEN, DATALENGTH |
LENGTH |
|
용례 |
SELECT LEN('NothingToUse')
SELECT DATALENGTH('NothingToUse')
SELECT LEN('지원하지 않음')
SELECT DATALENGTH('지원하지 않음') |
SELECT LENGTH('NothingToUse') FROM DUAL;
SELECT LENGTH('지원하지 않음') FROM DUAL; |
|
결과 |
12 / 12 / 7 / 13 |
12 / 7 |
è 위에서 보시면 알겠지만, BYTE 단위로 길이를 확인하시려면 LEN 이 아닌 DATALENGTH 를 사용하셔야 합니다.
16. NULL 일 경우 대체값 표시.
|
구분 |
MSSQL |
Oracle |
|
함수 |
ISNULL |
NVL |
|
용례 |
SELECT ISNULL(QTY1,100) FROM A_TEMP |
SELECT NVL(QTY1,100) FROM A_TEMP; |
|
결과 |
100 (값이 NULL일 경우) |
100 (값이 NULL 일 경우) |
17. 숫자형을 문자형으로 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
STR, CONVERT, CAST |
TO_CHAR |
|
용례 |
SELECT 123 + 456
SELECT STR(123) + STR(456)
SELECT STR(123,3,0) + STR(456,3,0) |
SELECT TO_CHAR(123) || TO_CHAR(456) FROM DUAL;
SELECT 123 || 456 FROM DUAL; |
|
결과 |
579
123 456
123456 |
123456
123456 |
è MSSQL의 STR는 기본 자릿수가 10자리입니다.
è Oracle에서는 위에서 보시다시피 숫자형태를 Concat하더라도 자동적으로 문자로 나옵니다.
è 물론 MSSQL에서는 CONVERT나 CAST를 훨씬 더 많이 사용합니다.
|
SELECT CONVERT(VARCHAR(3),123) + CONVERT(VARCHAR(3),456)
--> 결과
123456 |
18. 문자형을 숫자형으로 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
CONVERT, CAST |
TO_NUMBER |
|
용례 |
SELECT '123' + '456'
SELECT CONVERT(INT,'123') + CONVERT(INT,'456')
SELECT CAST('123' AS INT) + CAST('456' AS INT) |
SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL; |
|
결과 |
123456 / 579 / 579 |
579 |
19. 조건처리 구문
|
구분 |
MSSQL |
Oracle |
|
함수 |
CASE |
CASE (DECODE) |
è MSSQL CASE문은 여러 개의 조건 중 맞는 결과를 표시하는 것이고, Oracle의 DECODE는 참/거짓에 따라 결과를 표시합니다.
1) MSSQL CASE SAMPLE
|
-- 1. 테이블생 성
CREATE TABLE CASE_STUDY
(PKEY INT PRIMARY KEY,
DATA1 VARCHAR(20),
DATA2 VARCHAR(30)
);
-- 2. 자료 입력
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');
-- 3. 데이터 출력
SELECT
PKEY,
CASE
WHEN PKEY = 1 THEN DATA1
WHEN PKEY = 2 THEN DATA2
WHEN PKEY = 3 THEN DATA1
ELSE DATA2
END AS DATA
FROM
CASE_STUDY
;
-- 4. 결과
1 A -- 1일 때 DATA1값
2 나 -- 2일 때 DATA2값
3 C -- 3일 때 DATA1값
4 라 -- 아니면 DATA2값
5 마 -- 아니면 DATA2값 |
2) 같은 자료를 Oracle DECODE를 사용할 경우
|
-- 1. 테이블생 성
CREATE TABLE CASE_STUDY
(
PKEY NUMBER(9),
DATA1 VARCHAR2(20),
DATA2 VARCHAR2(30)
);
-- 2. 자료 입력
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');
-- 3. 데이터 출력
SELECT
PKEY,
DECODE(PKEY, 1, DATA1,
DECODE(PKEY, 2, DATA2,
DECODE(PKEY, 3, DATA1,
DATA2)
)
) AS DATA
FROM
CASE_STUDY
-- 또는
SELECT
PKEY,
DECODE(PKEY, 1, DATA1,
2, DATA2,
3, DATA1,
DATA2) AS DATA
FROM
CASE_STUDY
-- 4. 결과
1 A -- 1일 때 DATA1값
2 나 -- 2일 때 DATA2값
3 C -- 3일 때 DATA1값
4 라 -- 아니면 DATA2값
5 마 -- 아니면 DATA2값
|
è Oracle 8.1.7부터는 MSSQL과 거의 동일한 CASE문을 제공합니다.
위 구문을 MSSQL 구문과 동일하게 하셔도 결과는 같습니다.
20. 지금(Right Now) 가져오기
|
구분 |
MSSQL |
Oracle |
|
함수 |
GETDATE() |
SYSDATE |
|
용례 |
SELECT GETDATE() |
SELECT SYSDATE FROM DUAL; |
|
결과 |
2010-11-07 11:50:08.700 |
2010/11/07 11:50:09
|
è MSSQL 2008에서는 “SYSDATETIME()” 을 통해서 더 상세하게 사용이 가능합니다.(DATETIME2)
|
SELECT SYSDATETIME()
-- 결과
2010-11-07 11:52:25.9900000 |
21. 일자 더하기 / 빼기
|
구분 |
MSSQL |
Oracle |
|
함수 |
DATEADD |
+ / - |
|
용례 |
SELECT GETDATE();
SELECT DATEADD(d,1,GETDATE());
SELECT DATEADD(hh,5,DATEADD(d,1,GETDATE()));
SELECT DATEADD(d,-1,GETDATE()); |
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE + 1 FROM DUAL;
SELECT SYSDATE + 1.5 FROM DUAL; |
|
결과 |
2010-11-07 11:57:38.140
2010-11-08 11:57:38.140
2010-11-08 16:57:38.140
2010-11-06 11:57:38.140 |
2010/11/07 11:58:09
2010/11/08 11:58:09
2010/11/08 23:58:09
2010/11/06 11:58:09 |
è 위 결과를 보시면 아시겠지만, Oracle에서는 소수점 단위로 일자계산이 가능합니다. MSSQL에서는 일자에 소수점을 사용해도 인식이 불가능합니다.
22. 일자 차이 계산
|
구분 |
MSSQL |
Oracle |
|
함수 |
DATEDIFF |
+ / - |
|
용례 |
SELECT DATEDIFF(dd,'2010/10/07',GETDATE()) |
SELECT SYSDATE - TO_DATE('2010/10/07') FROM DUAL; |
|
결과 |
31 |
31.39103009259259259259259259259259259259 |
è 위 결과에도 나타나듯이, MSSQL에서는 일자로 지정하면 딱 떨어지지만, Oracle에서는 특별히 지정하지 않는 한 소수점까지 나타냅니다.
23. 해당 월의 마지막 날 가져오기
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
LAST_DAY |
|
용례 |
- |
SELECT LAST_DAY(to_date('2010/11/15', 'yyyy/mm/dd')) FROM DUAL; |
|
결과 |
- |
2010/11/30 00:00:00 |
è MSSQL에서는 아래와 같이 처리할 수 있습니다.
|
SELECT DATEADD(d,-1,CONVERT(DATETIME,CONVERT(CHAR(6),DATEADD(m,1,'2010-11-15'),112) + '01'))
-- 설명 : 해당일에 1개월을 더한 뒤, 그 달의 1일에서 하루를 뺀 날을 가져옵니다.
-- 결과
2010-11-30 00:00:00.000 |
24. Time Zone에 의한 시간 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
NEW_TIME |
|
용례 |
- |
SELECT NEW_TIME (TO_DATE ('2010/11/07 13:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL; |
|
결과 |
- |
2010/11/07 10:45:00 |
è AST : 대서양 표준시(캐나다 동부, 푸에르토리고, 버진아일랜드 등, 그리니치 표준시보다 4시간 늦음)
MST : 산악 표준시(로키 산맥에 가까운 미국과 캐나다 일부 지역의 동계 표준시, 그리니치 표준시보다 7시간 늦음)
è Oracle Zone 표
|
Value |
Description |
|
AST |
Atlantic Standard Time |
|
ADT |
Atlantic Daylight Time |
|
BST |
Bering Standard Time |
|
BDT |
Bering Daylight Time |
|
CST |
Central Standard Time |
|
CDT |
Central Daylight Time |
|
EST |
Eastern Standard Time |
|
EDT |
Eastern Daylight Time |
|
GMT |
Greenwich Mean Time |
|
HST |
Alaska-Hawaii Standard Time |
|
HDT |
Alaska-Hawaii Daylight Time |
|
MST |
Mountain Standard Time |
|
MDT |
Mountain Daylight Time |
|
NST |
Newfoundland Standard Time |
|
PST |
Pacific Standard Time |
|
PDT |
Pacific Daylight Time |
|
YST |
Yukon Standard Time |
|
YDT |
Yukon Daylight Time |
|
è MSSQL에 DATETIMEOFFSET이라는 것이 있지만, 이것은 표준시간대 인식일 뿐 구조는 다릅니다.
25. 해당일자 다음에 오는 해당 요일 반환
|
구분 |
MSSQL |
Oracle |
|
함수 |
지원하지 않음 |
NEXT_DAY |
|
용례 |
- |
SELECT NEXT_DAY('2010/11/07', '월요일') FROM DUAL; |
|
결과 |
- |
2010/11/08 00:00:00 |
è 보시다시피 2010년 11월 7일 이후에 처음 오는 월요일을 반환합니다.
하지만, 주의할 사항은 국가설정에 따라 일자설정이 다릅니다.
미국으로 되어 있으면 SELECT NEXT_DAY('10-Nov-07', 'MONDAY') FROM DUAL; 으로 하셔야 합니다.
26. 지정한 날짜의 특정 부분을 나타내는 문자열을 반환합니다.
|
구분 |
MSSQL |
Oracle |
|
함수 |
DATENAME, DATEPART … |
TO_CHAR |
|
용례 |
SELECT DATENAME(day, '2010-11-07')
SELECT DATENAME(month, '2010-11-07')
SELECT DATENAME(year, '2010-11-07')
SELECT DATEPART(day,'2010-11-07')
SELECT DAY('2010-11-07') |
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'DD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'MM') FROM DUAL;
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'YYYY') FROM DUAL; |
|
결과 |
7
11
2010
7
7 |
07
11
2010 |
è MSSQL DATENAME의 경우 2005 이전 버전에 맞춰져 있습니다. 그 이상의 버전에서는 용례 아랫부분을 참조하시면 됩니다.
Oracle의 경우엔 TO_CHAR를 만능으로 써서 할 수 있습니다.
다만 실제로 숫자처럼 쓰시려면 TO_NUMBER를 통해서 숫자형으로 변환해 주셔야 합니다.
27. 문자형을 날짜형으로 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
CONVERT / CAST |
TO_DATE |
|
용례 |
SELECT CONVERT(DATETIME,'2010-11-07')
SELECT CONVERT(DATETIME,'2010-11-07 12:20:23')
SELECT CONVERT(DATETIME,'20101107')
SELECT CONVERT(DATETIME,'20101107 12:20:23')
SELECT CAST('20101107 12:20:23' AS DATETIME) |
SELECT TO_DATE('2010-11-07') FROM DUAL;
SELECT TO_DATE('2010/11/07 12:20:23', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('20101107','YYYYMMDD') FROM DUAL;
SELECT TO_DATE('11/07/2010','MM/DD/YYYY') FROM DUAL; |
|
결과 |
2010-11-07 00:00:00.000
2010-11-07 12:20:23.000
2010-11-07 00:00:00.000
2010-11-07 12:20:23.000
2010-11-07 12:20:23.000 |
2010/11/07 00:00:00
2010/11/07 12:20:23
2010/11/07 00:00:00
2010/11/07 00:00:00 |
è Oracle의 경우엔 Format_Mask를 지정해 주시는 것이 관례입니다.
28. 날짜형을 문자형으로 변환
|
구분 |
MSSQL |
Oracle |
|
함수 |
CONVERT / CAST |
TO_CHAR |
|
용례 |
SELECT CONVERT(CHAR(8),GETDATE(),112)
SELECT CONVERT(CHAR(10),GETDATE(),120)
SELECT CONVERT(CHAR(20),GETDATE(),120)
SELECT CONVERT(CHAR(20),GETDATE())
SELECT CAST(GETDATE() AS CHAR(20)) |
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL; |
|
결과 |
20101107
2010-11-07
2010-11-07 17:41:21
11 7 2010 5:42PM
11 7 2010 5:41PM |
2010-11-07 17:43:53
20101107
11/07/2010 |
è 문자형과 날짜형 변환은 위처럼 사용이 가능한데요,
MSSQL에서의 자세한 사항은 강산아님의 아티클
(http://www.sqler.com/?mid=bColumn&page=4&document_srl=265068)
을 참조해 주세요.
