안녕하세요?
MSSQL과 Oracle 함수를 비교하신 분들이 많은데요. 의외로 예제를 실어 놓은 곳이 거의 없더군요. 하여 준비해 보았습니다.
하지만, 한 번에 너무 많은 것을 올려놓으면 보기 힘들기에…
오늘은 그 첫번째 시간으로 문자열 중 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
2010/11/06 11: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시간 늦음)
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를 만능으로 써서 할 수 있습니다.
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에서의 자세한 사항은 강산아님의 아티클
을 참조해 주세요.
일단, Oracle 과 MSSQL 함수 비교는 여기서 마칠까 합니다.
댓글 없음:
댓글 쓰기