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