[MSSQL] 자주쓰는 함수

[MSSQL] 자주쓰는 함수

날짜 형식(스타일,포맷) 변환 

 

http://featur.tistory.com/138

 

날짜시간타입을 정수형으로 변환 DATEPART

 

        SELECT 

GETDATE() 'GETDATE'

, DATEPART(DW, GETDATE()) 'DW' -- 일요일이 1 (설정가능)

, DATEPART(DAYOFYEAR, GETDATE()) 'DAYOFYEAR'

, DATEPART(QUARTER, GETDATE()) 'QUARTER'

, DATEPART(YEAR, GETDATE()) 'YEAR'

, DATEPART(MONTH, GETDATE()) 'MONTH'

, DATEPART(DAY, GETDATE()) 'DAY'

, DATEPART(HOUR, GETDATE()) 'HOUR'

, DATEPART(MINUTE, GETDATE()) 'MINUTE'

, DATEPART(SECOND, GETDATE()) 'SECOND'

, DATEPART(MILLISECOND, GETDATE()) 'MILLISECOND'

, DATEPART(MICROSECOND, GETDATE()) 'MICROSECOND'

, DATEPART(NANOSECOND, GETDATE()) 'NANOSECOND'

두 날짜 사이의 차이 분단위로 구하기 - DATEDIFF

   

SELECT DATEDIFF(DAY, '2016-07-05', '2016-09-01') 

 

이번달 마지막 날짜 구하기 (특정 일자의 월 마지막 날짜는 GETDATE()대신 특정 일자를 넣는다)

SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1,GETDATE()),-1);   

 

숫자함수

SELECT 

ISNUMERIC('123') --> 숫자 이면 1 리턴 

,    ISNUMERIC('ABC') --> 숫자 아니면 0 리턴

, ROUND(123.5,0) --> 124.0  반올림

, FLOOR(123.5) --> 123 버림

, FLOOR(123.5/10) *10 --> 120

, CEILING(123.5) --> 124 올림

, CEILING(123.5/10)*10 --> 130

 

- 신용카드 결제금액에서 공급가, 부가세 분리하기

    결제금액/1.1 를 반올림한 정수금액을 공급가,

    결제금액/11 을 반올림한 정수금액을 부가세로 하면 

    공급가 + 부가세= 결제금액이 된다. - ceiling(올림), floor(버림)은  원단위를 정확히 맞추기 어렵다.

  

    부가세는 11 로 나누면 소수점이 무조건 버려지므로 11.0 으로 나누어야 소수점으로 표현되어 반올림 효과가 난다.

    이것을 다시  FLOOR한 것은 결과값을 정수형으로 표현하기 위함이다.

  

    다음 처럼 ROUND한 다음 FLOOR해야 정수형이 된다.

   DECLARE @A  FLOAT = 2000    -- 8000

SELECT FLOOR(ROUND(@A/1.1, 0)) [SUP_AMT], FLOOR(ROUND(@A/11.0, 0)) [VAT_AMT]

 

숫자에 1000단위 콤마(,) 붙이기

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,123456),1),'.00','') -->123,456

문자함수

DECLARE @strString VARCHAR(100) = 'abcdefghijklmnopqrstuvwxyz1234567890가나다라마바사아자차카타파하'

SELECT

LEFT(@strString,3) -- 왼쪽 부터 원하는 만큼 문자읽어오기

, RIGHT(@strString,10) -- 오른쪽에서 부터 원하는 만큼 문자읽어오기

, SUBSTRING(@strString,4,5) -- 특정 위치에서부터 원하는 만큼 문자읽어오기

, LEN(@strString) -- 문자 단위 크기

, DATALENGTH(@strString) -- byte크기

, REPLICATE('0', 5 - DATALENGTH('123')) + '123' -- REPLICATE 지정한 횟수만큼 문자열 값을 반복합니다.

, 'add' + SPACE(5) + 'space' -- 지정한 횟수만큼 공백 추가

, STR(123) -- 숫자를 문자로 변환 ( 형 변환은 convert, cast 도 있음)

, CHARINDEX('0', @strString) -- 특정 문자열의 위치를 반환하는 함수

숫자 앞에 0 채우기   (0001, 0002 ~)

    

SELECT 

Right('000' + CONVERT(varchar(10), 123), 4)

, Right('000' + LTRIM(STR(123)), 4)

, REPLICATE('0', 4 - DATALENGTH('123')) + '123' 

 

CREATE FUNCTION LPAD(

@S VARCHAR(255), @N INT, @P VARCHAR(255) -- P는 앞에 채울 문자 주로 0

) 

RETURNS VARCHAR(255)

AS

BEGIN

RETURN ISNULL(REPLICATE(@P,@N-LEN(@S)),'')+@S

END

CREATE FUNCTION ZERO_PAD(            -- LPAD변형 앞자리에 0만채울수 있게

@S VARCHAR(255), @N INT

) 

RETURNS VARCHAR(255)

AS

BEGIN

RETURN ISNULL(REPLICATE('0',@N-LEN(@S)),'')+@S

END

널(NULL) 함수

ISNULL(A, '0') : A가 널이면 '0'으로 치환(오라클의 NVL함수)

NULLIF(A, B)   : A와 B가 같으면 널(NULL)값 반환

COALESCE(A,B,C,D) : A,B,C,D 중 최초로 널이 아닌값  

RAND() 함수

 

11부터 20까지 사이의 정수를 랜덤하게 추출하는 할 경우

DECLARE @FROM INT = 11

DECLARE @TO   INT = 20

SELECT CONVERT(INT, (@TO-@FROM+1) * RAND() + @FROM); 

-- NEWID 를 사용한 랜덤값 추출도 가능