DB

MSSQL 과 ORACLE 함수 비교

조셉LEE 2024. 2. 25. 14:01
728x90
반응형

MSSQL과 Oracle 함수 비교

SQL 2014.10.22 23:47 Posted by 목간쥐

 

고무고무 인간 블로그에서 퍼온글

 

 

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 DUAL2. 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 + 456SELECT 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)을 참조해 주세요.

 

출처 : http://www.sqler.com/269782  http://www.sqler.com/270809  http://www.sqler.com/316811

반응형
LIST

'DB' 카테고리의 다른 글

[MSSQL] RNAK() 함수 SEQ를 주거나 순위를 매길때  (1) 2024.02.26
UNPIVOT 숫자 형 변환 오류  (1) 2024.02.25
프로시져 cursor 상태 확인  (0) 2024.02.24
[MSSQL] 컬럼명으로 테이블 찾기  (1) 2024.02.24
조건절 IN 에 파라메타값 만들기 '1,2,3,4'  (0) 2024.02.24

'DB'의 다른글

  • 현재글MSSQL 과 ORACLE 함수 비교

관련글

댓글

티스토리툴바