유용한 쿼리문 MSSQL

(1) 일정 바이트 이상의 문자열 자르고 대체문자(...) 붙이기

DECLARE @v_Source VARCHAR(50)
DECLARE @v_Byte INT
DECLARE @v_Dest VARCHAR(50)

SET @v_Source = '안녕하세요 ABC 안녕!%&* 바보'
SET @v_Byte = 15

SET @v_Dest = (CASE WHEN DATALENGTH(@v_Source) > @v_Byte THEN SUBSTRING(CAST(@v_Source AS TEXT), 1, @v_Byte) + '...' ELSE @v_Source END)

SELECT @v_Dest

(2) 일정 문자 이상의 문자열 자르고 대체문자(...) 붙이기

DECLARE @v_Source VARCHAR(50)
DECLARE @v_Word INT
DECLARE @v_Dest VARCHAR(50)

SET @v_Source = '안녕하세요 ABC 안녕!%&* 바보'
SET @v_Word = 10

SET @v_Dest = (CASE WHEN LEN(@v_Source) > @v_Word THEN SUBSTRING(@v_Source, 1, @v_Word) + '...' ELSE @v_Source END)

SELECT @v_Dest

(3) 숫자 세자리 마다 콤마(쉼표) 찍기

① MONEY 데이터 형 이용

DECLARE @v_Source BIGINT
DECLARE @v_Dest VARCHAR(50)

SET @v_Source = 10023508023

SET @v_Dest = REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, @v_Source), 1), '.00', '')

SELECT @v_Dest

② FORMAT 함수 이용(MSSQL 2012 버전 부터 가능)

DECLARE @v_Source BIGINT
DECLARE @v_Dest VARCHAR(50)

SET @v_Source = 10023508023

SET @v_Dest = FORMAT(@v_Source, '###,0')

SELECT @v_Dest

(4) 최소, 최대 범위 값에서 난수 구하기

DECLARE @v_Min INT
DECLARE @v_Max INT
DECLARE @v_RndNum INT

SET @v_Min = 1
SET @v_Max = 100

SET @v_RndNum = CAST((@v_Max - @v_Min + 1) * RAND() + @v_Min AS INT)

SELECT @v_RndNum

(5) 생년월일로 만나이 계산

DECLARE @v_BirthDate DATETIME
DECLARE @v_Age INT

SET @v_BirthDate = '1980-01-03 00:00:00'

SET @v_Age = (CASE WHEN ISDATE(@v_BirthDate) = 1 THEN (DATEDIFF(DAY, @v_BirthDate, GETDATE()) - DATEDIFF(YEAR, @v_BirthDate, GETDATE()) / 4) / 365 ELSE -1 END)

SELECT @v_Age

(6) 주민번호로 만나이 계산

DECLARE @v_Jumin CHAR(14)
DECLARE @v_Age INT

SET @v_Jumin = '720103-1000000'
SET @v_Jumin = REPLACE(@v_Jumin, '-', '')

SET @v_Age = (YEAR(GETDATE()) -
CONVERT(SMALLINT, (CASE WHEN SUBSTRING(@v_Jumin, 7, 1) IN('1', '2') THEN '19'
WHEN SUBSTRING(@v_Jumin, 7, 1) IN('3', '4') THEN '20'
WHEN SUBSTRING(@v_Jumin, 7, 1) IN('9', '0') THEN '18' END) + LEFT(@v_Jumin, 2)) -
CASE WHEN SUBSTRING(@v_Jumin, 3, 4) >= SUBSTRING(CONVERT(CHAR(10), GETDATE(), 112), 5, 4) THEN 1 ELSE 0
END)

SELECT @v_Age

(7) 현재 월의 시작 일, 종료 일 구하기

DECLARE @v_StartDate VARCHAR(10)
DECLARE @v_EndDate VARCHAR(10)

SET @v_StartDate = CONVERT(VARCHAR(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0), 121)
SET @v_EndDate = CONVERT(VARCHAR(10), DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)), 121)

SELECT @v_StartDate, @v_EndDate

(8) 이번 주, 다음 주 특정 요일 날짜 구하기

① 이번 주

DECLARE @v_CurDateTime DATETIME
DECLARE @v_Week TINYINT

SET @v_CurDateTime = GETDATE()
SET @v_Week = 1 -- 1/2/3/4/5/6/7 : 일/월/화/수/목/금/토

SELECT CONVERT(CHAR(10), DATEADD(d, DATEPART(dw, @v_CurDateTime) * -1 + @v_Week, @v_CurDateTime), 120)

② 다음 주

(9) 타임 스탬프 구하기

DECLARE @v_CurDateTime DATETIME
DECLARE @v_UpdateDateTime DATETIME
DECLARE @v_TimeStamp INT

SET @v_CurDateTime = GETDATE()
SET @v_TimeStamp = DATEDIFF(s, '01/01/1970 00:00:00', @v_CurDateTime) + DATEDIFF(s, GETDATE(), GETUTCDATE())
SET @v_UpdateDateTime = DATEADD(s, @v_TimeStamp - DATEDIFF(s, GETDATE(), GETUTCDATE()), '01/01/1970 00:00:00')

SELECT @v_CurDateTime, @v_TimeStamp, @v_UpdateDateTime

(10) 전화번호 하이픈(-) 넣기

DECLARE @v_TelNumber VARCHAR(11)
DECLARE @v_DescTelNumber VARCHAR(13)

SET @v_TelNumber = '021234567'
SET @v_TelNumber = LTRIM(RTRIM(@v_TelNumber))

SET @v_DescTelNumber = (CASE WHEN LEN(@v_TelNumber) = 7 THEN SUBSTRING(@v_TelNumber, 1, 3) + '-' + SUBSTRING(@v_TelNumber, 4, 4)
WHEN LEN(@v_TelNumber) = 8 THEN SUBSTRING(@v_TelNumber, 1, 4) + '-' + SUBSTRING(@v_TelNumber, 5, 4)
WHEN LEN(@v_TelNumber) = 9 THEN SUBSTRING(@v_TelNumber, 1, 2) + '-' + SUBSTRING(@v_TelNumber, 3, 3) + '-' + SUBSTRING(@v_TelNumber, 6, 4)
WHEN LEN(@v_TelNumber) = 10 THEN
(
CASE WHEN LEFT(@v_TelNumber, 2) = '02' THEN SUBSTRING(@v_TelNumber, 1, 2) + '-' + SUBSTRING(@v_TelNumber, 3, 4) + '-' + SUBSTRING(@v_TelNumber, 7, 4)
ELSE SUBSTRING(@v_TelNumber, 1, 3) + '-' + SUBSTRING(@v_TelNumber, 4, 3) + '-' + SUBSTRING(@v_TelNumber, 7, 4)
END
)
WHEN LEN(@v_TelNumber) = 11 THEN SUBSTRING(@v_TelNumber, 1, 3) + '-' + SUBSTRING(@v_TelNumber, 4, 4) + '-' + SUBSTRING(@v_TelNumber, 8, 4)
ELSE @v_TelNumber
END)

SELECT @v_DescTelNumber AS TelNumber

유용한 쿼리문 MSSQL - 5

티스토리툴바