[MSSQL]유용한 함수들(문자, 조건 등)

✔ ISNULL() : 해당 컬럼 값이 NULL일 경우 지정한 값으로 대체

SELECT 직원이름, 성별, 퇴사일 FROM 직원; 
SELECT 직원이름, 성별, ISNULL(퇴사일, '9999-12-31') 퇴사일 FROM 직원;

​

✔ COALESCE() : ISNULL()과 동일함 ( 쿼리 범용성을 위해서 ISNULL() 함수 대신 사용 가능)

SELECT 직원이름, 성별, COALESCE(퇴사일, '9999-12-31') 퇴사일 FROM 직원;

​

✔ CONCAT() : 문자열 연결

SELECT CONCAT('나는 ',20,'살 입니다.') AS 문장; /* 나는 20살 입니다. */

✔ CONCAT_WS() : 나열된 값을 구분자를 두어서 모두 결합한 하나의 문자열로 반화

SELECT CONCAT_WS(',','킬리','매즈','희순') ; /* 킬리,매즈,희순 */

✔ CONVERT() : 데이터 형식 변환

SELECT CONVERT(varchar(10), 1234567) + '원 입니다.';

✔ CAST() : 데이터 형식 변환

SELECT CAST(1234567 AS varchar(10)) + '원 입니다.';

✔ FORMAT() : 표시되는 형식을 임의 형태로 지정

SELECT 고객이름, 
	   FORMAT(가입일, 'yyyy년MM월dd일') as 가입일, 
		/* #,# 으로 쓰면 0일 경우는 빈칸으로 출력됨. 
  		   #,0 으로 써주면 0 데이터 출력됨. */ 
       FORMAT(포인트, '#,#') + '포인트 'AS 포인트1, 
       FORMAT(포인트, '#,0') + '포인트 'AS 포인트2 
FROM 고객;

✔ TOP : 테이블 내 상위 하나의 행만 출력

SELECT TOP(1) * -- TTOP 1 로 써줘도 됨 
  FROM 고객 
  ORDER BY 가입일 DESC;

✔ TOP .. WITH TIES : 동일한 데이터가 있는 경우, 여러행 출력 가능

SELECT TOP 1 WITH TIES * 
FROM 고객 
ORDER BY 가입일 DESC;

📃 자세한 예시!

다음과 같은 데이터를 가진 테이블(T)이 있을 경우,

Table T

SELECT TOP 1 * -- 10 : 1건 출력 
--SELECT TOP 1 WITH TIES T.* -- 10, 10 : 2건 출력 
--SELECT TOP 2 WITH TIES T.* -- 10, 10: 2건 출력 
--SELECT TOP 3 WITH TIES T.* -- 10, 10, 9, 9: 4건 출력 
--SELECT TOP 4 WITH TIES T.* -- 10, 10, 9, 9: 4건 출력 
FROM ( SELECT 10 AS VAL UNION ALL 
	   SELECT 10 UNION ALL 
       SELECT 9 UNION ALL 
       SELECT 9 UNION ALL 
       SELECT 8 UNION ALL 
       SELECT 7 UNION ALL 
       SELECT 6 UNION ALL 
       SELECT 5 ) AS T 
 ORDER BY T.VAL DESC ;

​

✔ IIF() : 조건의 참/거짓에 따른 값 반환 (대다수의 경우 CASE문을 대체함)

SELECT IIF(10>20, '참','거짓'); -- 거짓

✔ CHOOSE() : 나열된 값 중에서 특정 번째 값 선택

SELECT CHOOSE(3,'나','너','우리','우리나라'); -- 우리

✔ STRING_AGG() : 문자열 집계 ( *** SQL SERVER 2017부터 가능)

SELECT STRING_AGG(고객이름,',') 성별_이름_집계 
FROM 고객 
WHERE 등급 = 'VIP' 
AND 포인트 > 110000 
AND 포인트 < 120000 
GROUP BY 성별;

✔ STRING_SPLIT() : 구분자로 연결된 문자열을 테이블 형태로 변화 ( FROM 절에 옴)

DECLARE @STR VARCHAR(100) = 'D0001,D0002,D0034,D0814'; --직원코드 
SELECT * 
FROM string_split(@STR, ',' ) AS V 
INNER JOIN 직원 AS E ON V.VALUE = E.직원코드;

✔ REPLICATE() : 특정 문자를 지정한 수만큼 반복

SELECT REPLICATE('*', 10); -- **********

✔ REPLACE()

SELECT 고객이름, 전자우편, REPLACE(전자우편,'gmeil.com','jmeil.com') AS 전자우편_NEW 
FROM 고객;

​

✔ LEFT() / RIGHT() : 왼쪽 / 오른쪽에서 지정한 길이의 문자열 추출

SELECT 고객이름, 전화번호, LEFT(전화번호,3) as 통신사 
FROM 고객; 
/* 고객이름 전화번호 통신사 
	김아기 010-9603-8126 010 */ 
    
SELECT 고객이름, 전화번호, RIGHT(전화번호,4) as 끝네자리 
FROM 고객; 
/* 고객이름 전화번호 끝네자리 
	김아기 010-9603-8126 8126 */

✔ SUBSTRING() : 지정한 위치부터 지정한 길이의 문자열 추출

✔ CHARINDEX() : 특정 문자 또는 문자열의 위치 확인

SELECT 고객이름, 전화번호, SUBSTRING(전화번호, 1,3) AS 통신사 
FROM 고객; 
/* 고객이름 전화번호 통신사 
	김아기 010-9603-8126 010 */ 
    
SELECT 고객이름, 전자우편, 
		CHARINDEX('@',전자우편) AS 위치, 
        SUBSTRING(전자우편, CHARINDEX('@',전자우편)+1, 100) AS 도메인주소 
 FROM 고객; 
 /* 고객이름 전자우편 위치 도메인주소
 	김아기 agi.kim@gmeil.com 8 gmeil.com */ 
 
 /* 도메인별 고객 수 */ 
 SELECT SUBSTRING(전자우편, CHARINDEX('@',전자우편)+1, 100) AS 도메인주소, 
 		COUNT(1) AS CNT 
  FROM 고객 
  GROUP BY SUBSTRING(전자우편, CHARINDEX('@',전자우편)+1, 100) 
  ORDER BY CNT DESC; 
  /* 도메인주소 CNT 
  	dbnuri.com 52 
    daun.net 51 
    naber.com 51 
    hotmeil.com 50 
    gmeil.com 47 
    hanmeil.com 38 */

✔ UPPER() / LOWER() : 대소문자 변환

✔ Ctrl + Shift + U / L

 DECLARE @str varchar(50) = 'Mads Mikkelsen';

 SELECT UPPER(@str); -- MADS MIKKELSEN
 SELECT LOWER(@str); -- mads mikkelsen

✔ SPACE() : 지정한 수만큼 공백(SPACE) 생김

SELECT 'ABC'+SPACE(10) +'DEF'; -- ABC DEF

✔ LTRIM() / RTRIM() / TRIM() : 공백제거

DECLARE @str char(20) = '          Mads   ';

SELECT '['+LTRIM(@str)+']'; -- [Mads      ]
SELECT '['+RTRIM(@str)+']'; -- [          Mads]
SELECT '['+TRIM(@str)+']'; -- [Mads]

✔ LEN() : 문자열에 포함된 문자 개수

/* 문자열에 포함된 'a' 개수 확인 */
 DECLARE @str varchar(100) = 'Mads Mikkelsen';
 SELECT @str 문자,
        LEN(@str) 문자길이,
		LEN(@str) - LEN(REPLACE(@str,'a','')) a개수 ;
/*
문자	문자길이	a개수
Mads Mikkelsen	14	1
*/

✔ DATALENGTH() : 문자열의 바이트(byte) 확인

✔ 숫자, 영문자 1byte, 한글 2byte

 DECLARE @str varchar(100) = 'Mads Mikkelsen 매즈 미켈슨';
 SELECT @str 문자,
        DATALENGTH(@str) 바이트 ; -- 26 ( 한글 (2*5) +  영문 (1*13) + 공백 (1*3) )

✔ REVERSE() : 문자열 또는 숫자를 뒤집은 결과 반환

 SELECT 고객코드, 고객이름, 포인트 ,
         REVERSE(고객이름) 이름거꾸로, 
		 REVERSE(포인트) 포인트거꾸로
  FROM 고객;
/*
고객코드	고객이름	포인트	이름거꾸로	포인트거꾸로
agikim	김아기	71500	기아김	00517
agoh	오암기	10800	기암오	00801
*/