[DBMS] 스칼라 함수

Dec 04, 2024

Contents

1. 구성 함수 2. 날짜 및 시간 함수 3. 수치 연산 함수 4. 메타 데이터 함수 5. 논리 함수 6. 문자열 함수 7. 예제

💡

스칼라 함수란 단일 값에 적용되어서 단일 값의 결과를 돌려주는 함수를 의미한다. 스칼라 함수의 종류는 구성 함수, 변환 함수, 커서 함수, 날짜 및 시간 함수, 논리 함수, 수치 연산 함수, 메타 데이터 함수, 보안 함수, 문자열 함수, 시스템 함수, 시스템 통계 함수, 텍스트 및 이미지 함수 등으로 나눠진다.

1. 구성 함수

💡

구성 함수는 현재 구성에 대한 정보를 알 수 있다.

@@LANGID @@LANGUAGE	현재 설정된 언어의 코드 번호 및 언어를 확인할 수 있다. 예) SELECT @@LANGID
@@SERVERNAME	현재 인스턴스의 이름을 확인할 수 있다. 예) SELECT @@SEVERNAME
@@SERVICENAME	서비스의 이름을 돌려준다. 기본 인스턴스인 경우 ‘MSSQLSERVER’ 를, 명명된 인스턴스는 설치할 때 지정한 인스턴스 이름을 돌려준다. 예) SELECT @@SERVICENAME
@@SPID	현재 사용자 프로세스의 세션 ID를 반환한다. 예) SELECT @@SPID AS 'ID', SYSTEM_USER AS [로그인사용자] , USER AS [사용자]
@@VERSION	현재 설치된 SQL Server의 버전, CPU 종류, 운영체제 버전의 정보를 알려준다. 예) SELECT @@VERSION;

2. 날짜 및 시간 함수

💡

날짜 및 시간 입력 값에 대한 함수이다.

SYSDATETIME() GETDATE()	현재 날짜와 시간을 알려준다. 예) SELECT SYSDATETIME() , GETDATE() ;
DATEADD()	날짜에 더한 결과를 돌려준다. 예) SELECT DATEADD(day,100,’2014/10/10’) ⇒ 2014년 10월 10일부터 100일 후의 날짜를 돌려준다. day 대신 year, month, week, hour, minute, second 등이 올 수 있다.
DATEDIFF()	두 날짜의 차이를 돌려준다. 예) SELECT DATEDIFF(week, GETDATE().’2022/10/19’); 현재부터 2022년 10월 19일까지 남은 주를 알려준다.
DATENAME()	날짜의 지정한 부분만 돌려준다. 예) SELECT DATENAME (weekday, ‘2022/10/19’); 2022년 10월 19일이 무슨 요일인지 알려준다.
DATEPART()	지정된 날짜의 년 또는 월 또는 일을 돌려준다. 예) SELECT DATEPART(year, GETDATE()); 현재 년도를 돌려준다.
DAY() MONTH() YEAR()	지정된 날짜의 일/월/년을 돌려준다. 예) SELECT MONTH(’2022/10/19’); 2022년 10월 19일의 월인 10을 돌려준다.
DATEFROMPARTS() DATETIME2FROMPARTS() DATETIMEFROMPARTS() DATETIMEOFFSETFROMPARTS() SMALLDATETIMEFROMPARTS() TIMEFROMPARTS()	문자열을 각각 입력하면 해당하는 데이터 형식의 값을 반환한다. 예를 들어, DATEFROMPARTS() 는 연, 월, 일을 입력하면 DTE 값을 반환한다. 예) SELECT DATEFROMPARTS('2022','10','19'); ’2022-10-19’ 를 반환한다.
EOMONTH()	입력한 날짜에 포함된 달의 마지막 날을 돌려준다. 예) SELECT EOMONTH(’2015-3-3’); 2015년 3월의 마지막 날짜를 돌려준다.

3. 수치 연산 함수

ABS()	수식의 절대값을 돌려준다. 예) SELECT ABS(-100) ; ⇒ 절대값인 100을 돌려준다.
ROUND()	자릿수를 올려서 돌려준다. 예) SELECT ROUND(1234.5678, 2), ROUND(1234,5678, -2); ⇒ 1234.5700 와 1200.0000을 돌려준다.
RAND()	0~1 까지의 임의의 숫자를 돌려준다. 예) SELECT RAND();
SQRT()	제곱근 값을 돌려준다. 예) SELECT SQRT(10); ⇒ 10의 제곱근인 약 3.16을 돌려준다.
POWER()	거듭제곱 값을 돌려준다. 예) SELECT POWER(3,2); ⇒ 3의 2제곱인 9를 돌려준다.

💡

이 외에도 DEGREES, ACOS, EXP, ASIN, FLOOR, SIGN, ATAN, LOG, SIN, ATN2, LOG10, CEILING, PI 등이 있다.

4. 메타 데이터 함수

💡

데이터베이스 및 데이터베이스 개체의 정보를 반환한다.

COL_LENGTH()	테이블 컬럼의 길이를 돌려준다. 예) USE sqlDB; SELECT COL_LENGTH(’userTb1’,’name’); NVARCHAR형이나 NCHAR형은 지정한 것보다 2배의 크기가 나온다.
DB_ID() DB_NAME()	DB의 ID 또는 DB의 이름을 돌려준다. 예) SELECT DB_ID(N’AdventureWorks2022’) ; SELECT DB_NAME(5) ; DB_ID()는 AdventureWorks2022 DB의 ID번호를 돌려주고 , DB_NAME()은 5번 DB의 이름을 돌려준다.
OBJECT_ID() OBJECT_NAME()	Object ID 또는 Object의 이름을 돌려준다. 예) SELECT OBJECT_ID(N’sqlDB.dbo.userTb1’); SELECT OBJECT_NAME(245575913); OBJECT_ID() 는 개체의 ID를 돌려주고, OBJECT_NAME() 은 개체 이름을 돌려준다.

5. 논리 함수

💡

논리 연산을 수행한다.

CHOOSE()	여러 값 중에서 지정된 위치의 값을 반환한다. 예) SELECT CHOOSE(2, ‘SQL’, ‘Server’, ‘2012’, ‘DBD’)L 2번째 문자열인 ‘Server’ 를 반환한다.
IIF()	파라미터로 수식, 참일 때, 거짓일 때 3가지가 사용된다. 예) SELECT IIF(100 > 200, ‘맞다’, ‘틀리다’);

6. 문자열 함수

💡

문자열을 조작한다.

ASCII() CHAR()	문자의 아스키 코드 값을 돌려주거나 아스키 코드값의 문자를 돌려준다. 예) SELECT ASCII(’A’) , CHAR(65) ;
CONCAT()	둘 이상의 문자열을 연결한다. 예) SELECT CONCAT(’SQL’,’SEERVER’,’2012’)
UNICODE() NCHAR()	문자의 유니코드값을 돌려주거나 유니코드값의 문자를 돌려준다. 예) SELECT UNICODE(’가’), NCHAR(44032);
CHARINDEX()	문자열의 시작 위치를 돌려준다. 예) SELECT CHARINDEX(’Server’,’SQL Server 2012’); ’Server’ 가 시작하는 위치인 5를 돌려준다.
LEFT() RIGHT()	왼쪽, 오른쪽 지정 위치부터 지정한 수만큼을 돌려준다. 예) SELECT LEFT(’SQL Server 2012’, 3), RIGHT(’SQL Server 2012’,4); ’SQL Server 2012’ 에서 왼쪽의 세 글자 ‘SQL’ 과 오른쪽 네 글자 ‘2012’ 를 돌려준다.
SUBSTRING()	지정한 위치부터 지정한 개수의 문자를 돌려준다. 예) SELECT SUBSTRING(N’대한민국화이팅’,3,2); ’대한민국화아팅’ 에서 3번째부터 2글자인 ‘민국’을 돌려준다.
LEN()	문자열으 길이를 돌려준다. 예) SELECT LEN(’SQL Server 2012’);
LOWER() UPPER()	소문자를 대문자로, 대문자를 소문자로 변환한다. 예) SELECT LOWER(’abcdEFGH’), UPPER(’abcdEFGH’) ;
LTRIM() RTRIM()	왼쪽 공백문자 및 오른쪽 공백 문자를 제거한다. 예) SELECT LTRIM(’ 공백제거 ’), RTRIM(’ 공백제거 ’) ;
REPLACE()	문자열의 내용을 지정한 것으로 찾아서 바꾼다. 예) SELECT REPLACE(’SQL Server 2012’, ‘Server’, ‘서버’); ’Server’ 를 ‘서버로’ 변환한다.
REPLICATE()	문자열을 지정한 수만큼 반복한다. 예) SELECT REPLICATE(’SQL’,5); ’SQLSQLSQLSQLSQL’ 를 반환한다.
REVERSE()	문자열의 순서를 거꾸로 만든다. 예) SELECT REVERSE(’SQL Server 2012’); ’2102 revreS LQS’ 를 반환
SPACE()	공백을 지정한 수 만큼 반복한다. 예) SELECT SPACE(5) ; ⇒ ‘ ‘ (공백 5개 리턴 )
STR()	숫자를 문자로 변환한다. (CAST 나 CONVERT 를 사용 권장)
STUFF()	문자를 지정한 위치의 개수만큼 삭제 후, 새로운 문자를 끼워넣는다. 예) SELECT STUFF(’SQL 서버 2012’,5,2,’Server’); 5번째부터 2글자 ’서버’ 를 삭제 후 ‘Server’ 로 대체한다.
FORMAT()	지정된 형식으로 출력한다. 표준 형식은 FORMAT(value,format)을 갖는데 , value는 출력할 값이고 , format은 출력할 형식이다. 예) SELECT FORMAT(GETDATE(), ‘dd/MM/YYYY’); 현재 날짜의 ‘일-월-연도’ 를 돌려준다.

7. 예제


use tempDB

CREATE TABLE maxTbl(
	col1  varchar(max),
	col2 nvarchar(max)
)

INSERT INTO maxTbl VALUES (REPLICATE('A',1000000),REPLICATE('가',1000000));

SELECT LEN(col1) AS [VARCHAR(MAX)], LEN(col2) AS [NCHAR(MAX)] FROM maxTbl

maxTbl 테이블에 각 문자를 100만 번씩 반복한 대량의 데이터를 입력했다.

결과는 SQL 의 최대값인 VARCHAR는 8000 문자, NVARCHAR는 4000 문자까지만 들어간 것을 확인할 수 있다.

VARCHAR(max) 및 NVARCHAR(max) 데이터 형식에 8000바이트가 넘는 양을 입력하려면 입력할 문자를 CAST() 나 CONVERT() 함수로 형 변환을 시켜준 후에 입력해야 한다.


DELETE FROM maxTbl;

INSERT INTO maxTbl VALUES (
	REPLICATE(CAST('A' AS VARCHAR(max)),1000000), 
	REPLICATE(CONVERT(NVARCHAR(max),'가'),1000000)
);

SELECT LEN(col1) AS [VARCHAR(max)], LEN(col2) AS [NVARCHAR(MAX)] FROM maxTbl

이번에는 입렭된 데이터의 내용을 수정해보자. ‘A’는 ‘B’로, ‘가’는 ‘나’로 변경해보자.


UPDATE maxTbl SET col1 = REPLACE((SELECT col1 FROM maxTbl), 'A','B'), 
				  col2 = REPLACE((SELECT col2 FROM maxTbl), '가','나')

데이터가 잘 변경되었는지 확인해보자. SELECT 는 앞부분만 보이므로 SUBSTRING() 함수나 REVERSE() 함수를 써서 뒷 부분이 변경됐는지를 확인해보자.


SELECT REVERSE((SELECT col1 FROM maxTbl))
SELECT SUBSTRING((SELECT col2 FROM maxTbl),999991,10)

이번에는 데이터를 변경하는 함수인 STUFF() 와 UPDATE 에서 제공해주는 열이름.WRITE() 를 이용해서 데이터를 변경해보자. 어느 것의 성능이 좋은지도 확인해보자.

도구 > SQL Server Profiler 를 실행한다.

탬플릿은 TSQL_Duration을 선택한다.

먼저 STUFF() 를 이용해서 각 데이터의 마지막 10글자를 ‘C’와 ‘다’ 로 변경해보자.


UPDATE maxTbl SET
	col1 = STUFF((SELECT col1 FROM maxTbl),999991,10,REPLICATE('C',10)),
	col2 = STUFF((SELECT col2 FROM maxTbl),999991,10,REPLICATE('다',10))

이번에는 열이름.WRITE() 함수를 사용해서 제일 끝 5글자를 ‘D’와 ‘라’로 변경해보자.


UPDATE maxTbl SET col1.WRITE('DDDDD',999996,5), col2.WRITE('라라라라라',999996,5)

프로파일러의 성능 차이를 확인했을 때 , STUFF() 함수에 비해 .WRITE() 함수가 더 빠른 것을 확인할 수 있다. 그러므로 VARCHAR(MAX) 및 NVARCHAR(MAX) 데이터 형식의 내용을 수정하는 경우에는 되도록 .WRITE() 함수를 사용하자.


SELECT REVERSE((SELECT col1 FROM maxTbl))
SELECT SUBSTRING((SELECT col2 FROM maxTbl),999991,10)

데이터가 정상적으로 변경되었다.

Contents

1. 구성 함수 2. 날짜 및 시간 함수 3. 수치 연산 함수 4. 메타 데이터 함수 5. 논리 함수 6. 문자열 함수 7. 예제

use tempDB CREATE TABLE maxTbl( col1 varchar(max), col2 nvarchar(max) ) INSERT INTO maxTbl VALUES (REPLICATE('A',1000000),REPLICATE('가',1000000)); SELECT LEN(col1) AS [VARCHAR(MAX)], LEN(col2) AS [NCHAR(MAX)] FROM maxTbl

DELETE FROM maxTbl; INSERT INTO maxTbl VALUES ( REPLICATE(CAST('A' AS VARCHAR(max)),1000000), REPLICATE(CONVERT(NVARCHAR(max),'가'),1000000) ); SELECT LEN(col1) AS [VARCHAR(max)], LEN(col2) AS [NVARCHAR(MAX)] FROM maxTbl