본문 바로가기
개발/database

[mssql] 오라클 사용자를 위한 MSSQL 함수

by 가시죠 2021. 1. 17.
반응형

오라클만 사용한 개발자가 MSSQL에서 쿼리를 작성할 경우 오라클에서 유용하게 사용한 함수가 없어서 MSSQL 문법에 맞는 함수 또는 형식으로 함수를 생성하여 사용할 경우 시간이 허비 되는 경우가 많다.

아래 함수를 MSSQL에 만들어두면 좀 더 편하게 쿼리를 작성할 수 있다.

 

 

--==================================================
-- 오라클 add_months를 mssql에서 사용하기 위한 함수
--==================================================
CREATE FUNCTION [dbo].[ADD_MONTHS] (  
 @date datetime 
, @m  INTEGER 
) 
RETURNS datetime 
AS 
BEGIN 
	return dateadd(MONTH,@m,@date)
END 

-- 사용법
SELECT dbo.ADD_MONTHS(GETDATE(), 1);



--=========================
-- 공백문자를 null로 리턴
--=========================
CREATE FUNCTION [dbo].[BLANK_TO_NULL]
(@field varchar(255))
RETURNS varchar(255)
AS
BEGIN
IF LTRIM(@field) = ''
	RETURN NULL
RETURN @field
END

-- 사용법
SELECT dbo.BLANK_TO_NULL('');
SELECT dbo.BLANK_TO_NULL(' ');
SELECT dbo.BLANK_TO_NULL(NULL);



--============================
-- 문자열을 날짜형식으로 리턴
--============================
CREATE FUNCTION [dbo].[CONVERT_STR_TO_DATETIME] (  
 @str VARCHAR(14)
) 
RETURNS DATETIME 
AS 
BEGIN 
	return CONVERT(DATETIME,LEFT(@str,8),112) +CONVERT(DATETIME, SUBSTRING(@str, 9,2) + ':' + SUBSTRING(@str, 11,2) + ':' + SUBSTRING(@str, 13,2), 108)
END 

-- 사용법
SELECT dbo.CONVERT_STR_TO_DATETIME('20190902102520')

--========================
-- 날짜형식을 문자로 리턴
--========================
CREATE  FUNCTION [dbo].[DATE2STRING](
  @_fieldDate DATETIME, 
  @_timeType  VARCHAR(10)
)

RETURNS VARCHAR(40)
AS
BEGIN

	DECLARE @_dateData VARCHAR(40)
	DECLARE @_convertDate VARCHAR(40)
	
	SET @_convertDate = CONVERT(VARCHAR, @_fieldDate, 121)

	IF isNull(@_convertDate, '') = '' or RTrim(LTrim(@_convertDate)) = ''
		SET @_dateData = ''
	ELSE
	BEGIN
		SET @_dateData =  SUBSTRING(@_convertDate, 1, 4) + SUBSTRING(@_convertDate, 6, 2) +  SUBSTRING(@_convertDate, 9, 2)
		IF @_timeType = 'H'
			SET @_dateData = @_dateData +  SUBSTRING(@_convertDate, 12, 2)
		ELSE IF  @_timeType = 'HM'
			SET @_dateData = @_dateData + SUBSTRING(@_convertDate, 12, 2) + SUBSTRING(@_convertDate, 15, 2)
		ELSE IF  @_timeType = 'HMS'
			SET @_dateData = @_dateData + SUBSTRING(@_convertDate, 12, 2) + SUBSTRING(@_convertDate, 15, 2) + SUBSTRING(@_convertDate, 18, 2)
	
	END

	RETURN @_dateData
END

-- 사용법
SELECT dbo.DATE2STRING(GETDATE(),'H');   --> 2019100213
SELECT dbo.DATE2STRING(GETDATE(),'HM');  --> 201910021334
SELECT dbo.DATE2STRING(GETDATE(),'HMS'); --> 20191002133433



--====================================
-- 특정 문자열이 어디에 위치해 있는지 리턴
--====================================
CREATE FUNCTION [dbo].[INSTR] (  
 @String1 nvarchar(max) 
, @String2 nvarchar(max) 
, @Start INTEGER = 1 
) 
RETURNS INTEGER 
AS 
BEGIN 
	WHILE LEN(@String1) - @Start > = 0 
	BEGIN  
		IF SUBSTRING(@String1, @Start, LEN(@String2)) = @String2  
			BREAK 
			SET @Start = @Start + 1  
	END 
	IF @Start > LEN(@String1)  
	SELECT @Start = 0 
	RETURN @Start 
END 

-- 사용법
SELECT dbo.INSTR('ABCDEFGABC','ABC',1);  --> 1
SELECT dbo.INSTR('ABCDEFGABC','ABC',2);  --> 8



--================================
-- 오라클의 LPAD를 mssql에서 사용
--================================
CREATE FUNCTION [dbo].[LPAD]
 (
  @mstr AS varchar(max),
  @nofchars AS int,
  @fillchar AS varchar(max)=' '
 )
RETURNS varchar(max)
AS
BEGIN
 RETURN
  CASE
   WHEN LEN(@mstr) >= @nofchars THEN LEFT(@mstr, @nofchars)
   ELSE
    LEFT(REPLICATE(@fillchar, @nofchars), @nofchars-LEN(@mstr)) + @mstr
  END
END

-- 사용법
SELECT dbo.LPAD('11',5,'0');  --> 00011




--=======================================
-- 숫자에 콤마를 붙여 특정 포맷으로 리턴
--=======================================
-- @_fieldData를 ','를 찍어서 출력
-- @_type 에 따라 아래 형식으로 변환
-- 1 : 999,999,999,999 
-- 2 : 999,999,990,990.90
-- 3 : 999,999,999,999.00
-- 4 : 999,999,999,990.0099
-- 5 : 999,999,999,999.990
-- 6 : 999,999,999,990.000
-- 7 : 999,999,999,990.9999 (정수라도 소수점이 찍혀서 출력 123 > 123.)
-- 8 : 999,999,990,990.0

CREATE FUNCTION [dbo].[NUMBERFORMAT]
(@_fieldData varchar(40), @_type numeric(2, 0))
RETURNS varchar(40)
WITH EXEC AS CALLER
AS
BEGIN
DECLARE @_numberData VARCHAR(40)
DECLARE @_decimalData VARCHAR(5)
DECLARE @_formatData VARCHAR(40)
DECLARE @_cnt INT

   IF isNull(@_fieldData, '') = '' or RTrim(LTrim(@_fieldData)) = ''
      BEGIN
         IF @_type = 6
            BEGIN
               Set @_formatData = '0.000'
            END
         ELSE IF @_type = 4
            BEGIN
               Set @_formatData = '0.00'
            END
         ELSE
            BEGIN
               Set @_formatData = '0'
            END
         Set @_type = 0
      END

   IF @_type = 1
      BEGIN
         Set @_numberData = Convert(varchar(40), convert(money, @_fieldData), 1)
         IF CharIndex('.', @_numberData) > 0 
            BEGIN
               Set @_formatData = Substring(@_numberData, 1, CharIndex('.', @_numberData)-1)
            END
 
      END 
    ELSE IF @_type = 2 or @_type = 3
      BEGIN 
         Set @_formatData = Convert(varchar(40), convert(money, @_fieldData), 1)
      END

   ELSE IF @_type = 5
      BEGIN
         Set @_numberData = Convert(varchar(40), convert(money, @_fieldData), 1)
         IF CharIndex('.', @_numberData) > 0
             BEGIN
                Set @_numberData = Substring(@_numberData, 1, CharIndex('.', @_numberData)-1)
                Set @_decimalData = Substring(@_fieldData, CharIndex('.', @_fieldData)+1, Len(@_fieldData))
 
                IF CharIndex('.', @_fieldData) > 0
                   BEGIN
	        IF Len(@_decimalData) >= 4
	            BEGIN
	               Set @_formatData = @_numberData +'.'+ Substring(@_decimalData, 1, 3)
	            END
	         ELSE IF Len(@_decimalData) = 3
	            BEGIN
	               Set @_formatData =@_numberData+'.'+ @_decimalData
	            END
	         ELSE IF Len(@_decimalData) = 2
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'0'
	            END                  
	         ELSE IF Len(@_decimalData) = 1
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'00'
	            END     
	         ELSE
	            BEGIN
	               Set @_formatData = @_numberData+'.000'
	            END     
                   END
                ELSE
                   BEGIN
                      Set @_formatData = @_numberData+'.000'
                   END
             END
      END

   ELSE IF @_type = 6
      BEGIN
         Set @_numberData = Convert(varchar(40), convert(money, @_fieldData), 1)
         IF CharIndex('.', @_numberData) > 0
             BEGIN
                Set @_numberData = Substring(@_numberData, 1, CharIndex('.', @_numberData)-1)
                Set @_decimalData = Substring(@_fieldData, CharIndex('.', @_fieldData)+1, Len(@_fieldData))
 
                IF CharIndex('.', @_fieldData) > 0
                   BEGIN
	        IF Len(@_decimalData) >= 4
	            BEGIN
	               Set @_formatData = @_numberData +'.'+ Substring(@_decimalData, 1, 3)
	            END
	         ELSE IF Len(@_decimalData) = 3
	            BEGIN
	               Set @_formatData =@_numberData+'.'+ @_decimalData
	            END
	         ELSE IF Len(@_decimalData) = 2
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'0'
	            END                  
	         ELSE IF Len(@_decimalData) = 1
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'00'
	            END     
	         ELSE
	            BEGIN
	               Set @_formatData = @_numberData+'.000'
	            END     
                   END
                ELSE
                   BEGIN
                      Set @_formatData = @_numberData+'.000'
                   END
             END
      END

   ELSE IF @_type = 7
      BEGIN
         Set @_numberData = Convert(varchar(40), convert(money, @_fieldData), 1)
         IF CharIndex('.', @_numberData) > 0
             BEGIN
                Set @_numberData = Substring(@_numberData, 1, CharIndex('.', @_numberData))
                Set @_decimalData = Substring(@_fieldData, CharIndex('.', @_fieldData)+1, Len(@_fieldData))
 
                IF CharIndex('.', @_fieldData) > 0
                   BEGIN
	        IF Len(@_decimalData) >= 4
	            BEGIN
	               Set @_formatData = @_numberData + Substring(@_decimalData, 1, 3)
	            END
	         ELSE IF Len(@_decimalData) = 3
	            BEGIN
	               Set @_formatData =@_numberData+'.'+ @_decimalData
	            END
	         ELSE IF Len(@_decimalData) = 2
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'0'
	            END                  
	         ELSE IF Len(@_decimalData) = 1
	            BEGIN
	               Set @_formatData = @_numberData+'.'+ @_decimalData +'00'
	            END     
	         ELSE
	            BEGIN
	               Set @_formatData = @_numberData
	            END     
                   END
                ELSE
                   BEGIN
                      Set @_formatData = @_numberData +'.000'
                   END
             END
      END

   ELSE IF @_type = 8
      BEGIN
         Set @_numberData = Convert(varchar(40), convert(money, @_fieldData), 1)
         IF CharIndex('.', @_numberData) > 0
             BEGIN
                SET @_numberData = SUBSTRING(@_numberData, 1, CHARINDEX('.', @_numberData))
                SET @_decimalData = SUBSTRING(@_fieldData, CHARINDEX('.', @_fieldData)+1, LEN(@_fieldData))
 
                IF CHARINDEX('.', @_fieldData) > 0
                   BEGIN
	        IF LEN(@_decimalData) >= 2
	            BEGIN
	               SET @_formatData = @_numberData + SUBSTRING(@_decimalData, 1, 1)
	            END           
	         ELSE IF LEN(@_decimalData) = 1
	            BEGIN
	               SET @_formatData = @_numberData+ @_decimalData
	            END     
	         ELSE
	            BEGIN
	               SET @_formatData = @_numberData
	            END     
                   END
                ELSE
                   BEGIN
                      SET @_formatData = @_numberData +'0'
                   END
             END
      END

   ELSE IF @_type = 9
      BEGIN
         SET @_numberData = CONVERT(VARCHAR(40), CONVERT(MONEY, @_fieldData), 1)
         IF CHARINDEX('.', @_numberData) > 0
             BEGIN
                SET @_numberData = SUBSTRING(@_numberData, 1, CHARINDEX('.', @_numberData)-1)
                SET @_decimalData = SUBSTRING(@_fieldData, CHARINDEX('.', @_fieldData)+1, LEN(@_fieldData))
 
                IF CHARINDEX('.', @_fieldData) > 0
                   BEGIN
	        IF LEN(@_decimalData) >= 5
	            BEGIN
	               SET @_formatData = @_numberData +'.'+ SUBSTRING(@_decimalData, 1, 4)
	            END
	         ELSE IF LEN(@_decimalData) = 4
	            BEGIN
	              SET @_formatData =@_numberData+'.'+ @_decimalData
	            END
	         ELSE IF LEN(@_decimalData) = 3
	            BEGIN
	               SET @_formatData =@_numberData+'.'+ @_decimalData +'0'
	            END
	         ELSE IF LEN(@_decimalData) = 2
	            BEGIN
	               SET @_formatData = @_numberData+'.'+ @_decimalData +'00'
	            END                  
	         ELSE IF LEN(@_decimalData) = 1
	            BEGIN
	               SET @_formatData = @_numberData+'.'+ @_decimalData +'000'
	            END     
	         ELSE
	            BEGIN
	               SET @_formatData = @_numberData+'.0000'
	            END     
                   END
                ELSE
                   BEGIN
                      SET @_formatData = @_numberData+'.0000'
                   END
             END
      END

   ELSE IF @_type = 4
      BEGIN
         SET @_numberData = CONVERT(VARCHAR(40), CONVERT(MONEY, @_fieldData), 1)
         IF CHARINDEX('.', @_numberData) > 0 
            BEGIN
               SET @_numberData = SUBSTRING(@_numberData, 1, CHARINDEX('.', @_numberData)-1)

               IF CHARINDEX('.', @_fieldData) > 0
                  BEGIN
                     SET @_decimalData = SUBSTRING(@_fieldData, CHARINDEX('.', @_fieldData)+1, LEN(@_fieldData))
                     IF LEN(@_decimalData) > 4
                         BEGIN
                            SET @_decimalData = SUBSTRING(@_decimalData, 1, 4)
                         END

                     IF @_decimalData = '0000'
                        BEGIN
                           SET @_decimalData = '00'
                        END
                  END
               ELSE
                  BEGIN
                     SET @_decimalData = '00'
                  END

               IF LEN(@_decimalData) = 4
                  BEGIN
                     IF RIGHT(@_decimalData, 1) = 0
                        BEGIN
                           SET @_decimalData = '.'+ SUBSTRING(@_decimalData, 1, 3)
                        END                     
                     ELSE
                        BEGIN
                           SET @_decimalData = '.'+ @_decimalData
                        END
                  END

               IF LEN(@_decimalData) = 3
                  BEGIN
                     IF RIGHT(@_decimalData, 1) = 0
                        BEGIN
                           SET @_decimalData = '.'+ SUBSTRING(@_decimalData, 1, 2)
                        END                     
                     ELSE
                        BEGIN
                           SET @_decimalData = '.'+ @_decimalData
                        END
                  END              

               IF LEN(@_decimalData) = 2
                  BEGIN
                     SET @_decimalData = '.'+ @_decimalData
                  END      

               IF LEN(@_decimalData) = 1
                  BEGIN
                     SET @_decimalData = '.'+ @_decimalData +'0'
                  END
           
            END
         ELSE
            BEGIN
               SET @_decimalData = '.'+ '00'
            END
        
            SET @_formatData = @_numberData + @_decimalData     

      END

	  IF(CHARINDEX('.',@_formatData) > 0)
		BEGIN
			SET @_cnt = LEN(@_formatData)
			WHILE @_cnt > 0
				BEGIN
					IF(RIGHT(@_formatData,1) = '0')
						BEGIN
							SET @_formatData = LEFT(@_formatData, LEN(@_formatData) - 1)
							SET @_cnt = @_cnt - 1
						END
					ELSE
						BEGIN
							IF( RIGHT(@_formatData,1) = '.' )
								SET @_formatData = LEFT(@_formatData, LEN(@_formatData) - 1)
							BREAK;
						END
				END
		END
		

   RETURN @_formatData
END

-- 사용법
-- 1 : 999,999,999,999 
-- 2 : 999,999,990,990.90
-- 3 : 999,999,999,999.00
-- 4 : 999,999,999,990.0099
-- 5 : 999,999,999,999.990
-- 6 : 999,999,999,990.000
-- 7 : 999,999,999,990.9999 (정수라도 소수점이 찍혀서 출력 123 > 123.)
-- 8 : 999,999,990,990.0




--===============================
-- 오라클의 NVL을 mssql에서 사용
--===============================
CREATE  FUNCTION [dbo].[NVL] (
  @_field VARCHAR(255),
  @_nullval VARCHAR(255)

)
RETURNS VARCHAR(255)
AS

BEGIN
  RETURN ISNULL(NULLIF( @_field, '' ), @_nullval)

END

-- 사용법
SELECT dbo.nvl(null, 'A')  --> A
SELECT dbo.nvl('', 'A')  --> A
SELECT dbo.nvl('  ', 'A')  --> A



--============================
-- 문자열을 숫자형식으로 리턴
--============================
CREATE  FUNCTION [dbo].[STR_TO_NUM] (
  @_field VARCHAR(255)
)
RETURNS float
AS

BEGIN
  if isnumeric(@_field)=1
    return convert(float ,@_field)
  return null

END

-- 사용법
SELECT dbo.STR_TO_NUM('123456')  --> 123456



--===================================
--오라클의 substr 을 mssql에서 사용
--===================================
CREATE function [dbo].[substr](@X[varchar](max),@start[int],@n[int]=0)
RETURNS [varchar](max)
AS
BEGIN
 declare @retval varchar(max)
 set @retval = substring(@X,@start,@n)
 return @retval
END

-- 사용법
SELECT dbo.SUBSTR('ABCDEFG',1,3); --> ABC
SELECT dbo.SUBSTR('ABCDEFG',2,3); --> BCD



--============================================
-- 오라클의 sysdate를 mssql에서 유사하게 사용
--============================================
/**
 * 현재의 시스템 날자를 반환함.
 * 파라미터 
 		: D => 'YYYYMMDD'
		: T => 'HH24MISS'
		: H => 'HH24MI'
 */
CREATE FUNCTION [dbo].[SYSDATE]( @RETURN_TYPE VARCHAR(1) )
RETURNS VARCHAR(14)
AS

BEGIN
	DECLARE  @RETURN_DATA VARCHAR(16)
	
	IF( @RETURN_TYPE = 'D' )
		BEGIN
			SET @RETURN_DATA = CONVERT(VARCHAR, GETDATE(), 112)
		END
	ELSE IF(@RETURN_TYPE = 'T')
		BEGIN
			SELECT @RETURN_DATA = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 114), ':', '')
		END
	ELSE IF(@RETURN_TYPE = 'H')
		BEGIN
			SELECT @RETURN_DATA = REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', '')
		END
	ELSE
		BEGIN
			SELECT @RETURN_DATA = CONVERT(VARCHAR, GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 114), ':', '')
		END

	RETURN ( @RETURN_DATA )
END

-- 사용법
SELECT dbo.SYSDATE('D');
SELECT dbo.SYSDATE('T');
SELECT dbo.SYSDATE('H');



--================================
--오라클의 trim을 mssql에서 사용
--================================
CREATE function [dbo].[TRIM](@X[VARCHAR](max))
returns [varchar](max)
AS
begin
 return  rtrim(ltrim(@x))
end

-- 사용법
SELECT '@@'+' A '+'@@';  --> @@ A @@
SELECT '@@'+dbo.TRIM(' A ')+'@@';  --> @@A@@



--==========================================
-- 오라클의 trunc를 mssql에서 유사하게 사용
--==========================================
create function [dbo].[TRUNC](@X[float],@n[int]=0)
RETURNS [float]
AS
BEGIN
 declare @retval float
 if @n is null or @x is null
 begin 
	set @retval = null
    return @retval
 end

if @x>0
 set @retval = floor(@x*power(cast(10 as float),@n))/ power(cast(10 as float),@n)
else 
 set @retval = ceiling(@x * power(cast(10 as float),@n)) /power(cast(10 as float),@n)
return @retval
END

-- 사용법
SELECT 123.456; --> 123.456
SELECT dbo.TRUNC(123.456,0);  --> 123
SELECT dbo.TRUNC(123.456,1);  --> 123.4
SELECT dbo.TRUNC(123.456,2);  --> 123.45
SELECT dbo.TRUNC(123.456,3);  --> 123.456
SELECT dbo.TRUNC(123.456,4);  --> 123.456



--=========================================
--mssql의 기본 내장함수 charindex의 반대
--=========================================
CREATE FUNCTION [dbo].[LASTINDEXOF] (
   @expressionToFind NVARCHAR (4000),
   @expressionToSearch NVARCHAR(MAX)
)
   RETURNS BIGINT
AS
BEGIN

	DECLARE @RETURN_DATA INT

	SELECT @RETURN_DATA = CHARINDEX(@expressionToFind, REVERSE(@expressionToSearch))
	
	RETURN @RETURN_DATA
END

-- 사용법
-- MS SQL에 기본 내장된 함수
SELECT CHARINDEX('B','ABCDEFG')  --> 2

-- MS SQL에 기본 내장된 함수 CHARINDEX의 반대
SELECT dbo.LASTINDEXOF('B', 'ABCDEFG')  --> 6
반응형

'개발 > database' 카테고리의 다른 글

oracle -> mssql 변경  (0) 2021.01.17
[oracle] 오라클 한글 3byte를 2byte로 변경하여 byte 자르기  (0) 2021.01.17
[oracle] 오라클 플래시백 쿼리 flashback query AS OF TIMESTAMP  (0) 2021.01.17
[oracle] 오라클 plsql loop sample  (0) 2021.01.17
[oracle] 오라클 날짜, 숫자를 원하는 포맷 문자열로 변환 to_char  (0) 2021.01.17

관련글

댓글

티스토리툴바