-
[MSSQL] 자주쓰는 함수
날짜 형식(스타일,포멧) 변환
날짜시간타입을 정수형으로 변환 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 를 사용한 랜덤값 추출도 가능
댓글 없음:
댓글 쓰기