안녕하세요?
MSSQL과 Oracle 함수를 비교하신 분들이 많은데요. 의외로 예제를 실어 놓은 곳이 거의 없더군요. 하여 준비해 보았습니다.
하지만,Vnbsp;한 번에 너무 많은 것을 올려놓으면 보기 힘들기에…
오늘은 그 첫번째 시간으로 문자열 중 10가지만 적어봅니다.
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번째 자리에서 시작해서 다섯 글자를 가져옵니다.
안녕하세요?지난 번에 이어 두 번째 Function 비교 시간입니다.
또다른 9가지 나갑니다. ^^.
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 구문과 동일하게 하셔도 결과는 같습니다.
안녕하세요?
지난 번에 이어 마지막으로 세 번째 Function 비교 시간입니다.
MSSQL 2005, Oracle 9i 기준입니다.
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 |
è 위 결과를 보시면 아시겠지만, Oracle에서는 소수점 단위로 일자계산이 가능합니다. MSSQL에서는 일자에 소수점을 사용해도 인식이 불가능합니다.
22. 일자 차이 계산
|
구분 |
MSSQL |
Oracle |
|
함수 |
DATEDIFF |
+ / - |
|
용례 |
SELECTDATEDIFF(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 표
|
è 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' ASDATETIME) |
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)
을 참조해 주세요.
일단, Oracle 과 MSSQL 함수 비교는 여기서 마칠까 합니다
출처 : http://blog.naver.com/reomereome/40119053027
'ETC' 카테고리의 다른 글
| 정규식 (0) | 2012.06.12 |
|---|---|
| CSS display 속성 - inline과 block (0) | 2012.05.31 |
| crontab 사용하기 (정기 작업) 스케줄러 (0) | 2012.04.06 |
| 이클립스(eclipse)에 자바 디컴파일러 연동하는 방법 (0) | 2012.02.10 |
| 이클립스에서 iBatis + log4j 로 콘솔창에 SQL 쿼리를 찍어보자 (0) | 2011.10.20 |
