개발/database
[mssql] 오라클 사용자를 위한 MSSQL 함수
가시죠
2021. 1. 17. 15:40
반응형
오라클만 사용한 개발자가 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
반응형