MS-SQL 날짜함수, 숫자함수, 문자함수, 널함수, 1000단위콤마

SQL Server

MS-SQL 날짜함수, 숫자함수, 문자함수, 널함수, 1000단위콤마

미스터몽키 2009. 10. 22. 22:59

-> 네이버로

★ 날짜함수

날짜시간타입을 --> 날짜만의 문자로 변환 ('2010-02-09 13:25:52' 이 화요일일때)

select convert(char(10), getdate(),121) --> '2010-02-09' (121스타일은 밀리세컨드 포함, 120스타일은 초까지만)

select convert(char(19), getdate(),121) --> '2010-02-09 13:25:52'

select convert(char(8), getdate(),112) --> '20100209'

select convert(char(8), getdate(),108) --> '22:46:12' (24시간체계 시:분:초)

select datename(dw, getdate()) --> '화요일'

select datename(mm, getdate()) --> '02'

select datename(yyyy, getdate()) --> '2010'

select datename(dd, getdate()) --> '9' (이상 '09'가 아니다)

SELECT CONVERT(char(8), GETDATE(), 112) + REPLACE(CONVERT(char(8), GETDATE(), 108), ':','') -> '20170125133545'

날짜시간타입을 --> 정수로 변환 ('2010-02-09'이 화요일일때)

select datepart(dw, getdate()) --> 3 (일요일이 1)

select datepart(month, getdate()) --> 2

select datepart(mm, getdate()) --> 2

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

@dt1 = '2012-06-29 07:10:00'

@dt2 = '2012-06-29 08:40:00' 일때

select DATEDIFF(mi, @dt1, @dt2) --> 90 (분단위)

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

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

★ 문자열 -> 날짜형 변환

SELECT CONVERT (DATE, '2019-01-04') -- 2019-01-04

, CONVERT (DATE, '20190104') -- 2019-01-04

, CONVERT (DATETIME, '2019-01-04') -- 2019-01-04 00:00:00.000

, CONVERT (DATETIME, '2019-01-04 09:15:52') -- 2019-01-04 09:15:52.000

★ 숫자함수

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

select FLOOR(123.5) --> 123 버림

select CEILING(123.5) --> 124 올림(소수점이하 올림)

select CEILING(123.5/10)*10 --> 130 올림(원단위 올림)

원단위 절사

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

원단위 절상

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

문자열이 숫자인지 검사 함수 (숫자면 1 숫자가 아니면 0)

ISNUMERIC('123') --> 1 리턴, ISNUMERIC('ABC') --> 0 리턴

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

결제금액/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

★ 문자함수

left, right : 왼쪽, 오른쪽에서 부터 원하는 만큼 문자읽어오기

substring : 특정 위치에서부터 원하는 만큼 문자읽어오기

select len('정상ab1'), datalength('정상ab1') --> 5 , 7 반환(len은 문자단이크기, datalength는 byte크기)

SELECT REPLICATE('0', 5 - DATALENGTH('123')) + '123' --> '00123' (오라클의 Lpad함수구현이랄까)

REPLACE('abc_d', '_', '') --> 'abcd' : 특정문자열을 찾아 다른 문자열로 바꾼다.

LTRIM, RTRIM --> 왼쪽, 오른쪽 공백문자열을 없앤다.

SPACE --> 공백을 지정한 수만큼 반복

STR : 숫자를 문자로 변환

CHARINDEX : 특정 문자열의 위치를 반환하는 함수

SELECT CHARINDEX('다', '가나다라마') -> 3 리턴 (처음부터 찾았을 때) 찾은 위치 처음부터 카운트

SELECT CHARINDEX('자', '가나다라마') -> 0 리턴

SELECT CHARINDEX('다', '가나다라마', 2 ) -> 3 리턴 (2번째부터 찾았을 때) 찾은 위치 처음부터 카운트

SELECT CHARINDEX('다', '가나다라마', 4) -> 0 리턴 (4번째부터 찾았을 때는 없으므로)

-- 특정문자 갯수 구하기

declare @s varchar(10) = 'abca1234'

SELECT LEN(@s) - LEN(REPLACE(@s, 'a', '')) -> 결과 2

★ ISNUMERIC 함수 (식이 숫자인지 아닌지)

식이 숫자이면 1을 아니면 0을 반환

SELECT ISNUMERIC('123'), -- 1 리턴

ISNUMERIC('ABC') -- 0 리턴

CONVERT(int, '') 은 0을 리턴하지만 CONVERT(int, 'abb') 는 에러를 발생한다.

따라서 컬럼A가 varchar 타입이고 숫자만 주로 입력되고 소트를 할때 숫자형으로 소트를 하고 싶을때

다음으로 처리할 수 있다.

SELECT CASE WHEN ISNUMERIC(컬럼A) = 1 THEN CONVERT(INT, 컬럼A) ELSE NULL END

즉 숫자형으로 변환 가능할 때만 CONVERT 함수를 적용하고 아니면 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);

★ 나이 계산

DECLARE @birth date ='2015-12-28' -- 혹은 varchar(10)
SELECT DATEDIFF(YEAR, @birth, GETDATE()) -- 년나이, 단순 년도 차이
SELECT FLOOR(CONVERT(int, DATEDIFF(day, @birth, GETDATE()))/365.2422) -- 정확한 만 나이 윤년도 계산된

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

TICKET_NO 가 int 형일때 2가지 방법이 있다.

1번 SELECT Right('000' + CONVERT(varchar(10), TICKET_NO), 4)
2번 SELECT Right('000' + LTRIM(STR(TICKET_NO)), 4)

/******************************************************

- 오라클의 Lpad함수(왼쪽에 '0'이나 특정문자 채우기) 구현

- ms-sql에서 사용자정의함수(UDF)로 만들기

*****************************************************/

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

/************************************************/