SQL Server Built-in Functions
SQL is very powerful procedural language that adds programming constructs to SQL giving opportunity to developers to build more sophisticated scripts and stored procedures.
SQL has a function structure which are predefined(system functions) and user defined functions besides stored procedures. With the help of these functions, it is getting more powerful to develop different kind of database solutions.
Today, I want to write about system functions with examples.
There are many kind of function types but I will focus on mostly used functions.
1. String Functions
String functions are used on textual data
a. ASCII
It shows the ASCII equivalent of the first character of string data it takes as a parameter. (As you see, “SQL” and “S” has same ASCII code because function is getting ASCII code only for first character)
SELECT ASCII('SQL') as "SQL",
ASCII('S') as S,
ASCII('Q') as Q,
ASCII('L') as L,
ASCII('s') as s,
ASCII('q') as q,
ASCII('l') as lsq
b. CHAR
It does the reverse operation of ASCII function. It takes ASCII code as a parameter and returns character value.
SELECT CHAR(83),CHAR(81),CHAR(76),CHAR(115),CHAR(113),CHAR(108)
c. CHARINDEX
It shows the rank of a searched value. It can be used by two or three parameters; searched_expression, expression_to_be_searched, start_position
select CHARINDEX('SQL', 'Advanced level SQL Server Coding Courses', 0);
same with
select CHARINDEX('SQL', 'Advanced level SQL Server Coding Courses');
select CHARINDEX('SQL', 'Advanced level SQL Server Coding Courses', 18);
-- When you point a start position after exact position, it returns zero.calc
d. CONCAT
CONCAT is used to combine two or more parameter value.
SELECT CONCAT('SQL','-','SERVER','-','DOCUMENTATIONS','-', 12,'-', getdate()) as Result
e. LEFT & RIGHT
LEFT allows taking the value specified to the left of an expression.
SELECT LEFT('Middle & Advanced Level Sql Server Course',17) as Result
RIGHT allows taking the value specified to the right of an expression.
SELECT RIGHT('Middle & Advanced Level Sql Server Course',17) as Result
f. TRIM, LTRIM, RTRIM
- TRIM: deletes the spaces from left and right of a text.
- LTRIM: deletes the spaces from left of a text
- RTRIM: deletes the spaces from right of a text
SELECT TRIM(' Middle & Advanced Level Sql Server Course ') as Result
SELECT LTRIM(' Middle & Advanced Level Sql Server Course ') as Result
SELECT RTRIM(' Middle & Advanced Level Sql Server Course ') as Result
g. UPPER, LOWER
- Upper: It is used to make text value to uppercase letters.
- Lower: It is used to make text value to lowercase letters.
SELECT UPPER('Middle & Advanced Level Sql Server Course') as Result
SELECT LOWER('Middle & Advanced Level Sql Server Course') as Result
h. REPLACE
It is used to replace a a text with another text.
SELECT REPLACE('Middle & Advanced Level Sql Server Course', '&' ,'and') as Result
i. SUBSTRING
It extracts a portion with a specified length starting from a location in an input string.
-- This query takes two character starting first charater in FirstName and
-- two chatrtes starting from third character in LastName
select BusinessEntityID, FirstName,SUBSTRING(FirstName,1,2), LastName, SUBSTRING(LastName,3,2)
from AdventureWorks2019.Person.Person
j. STRING_SPLIT
It splits a string into the table columns according to the given separator parameter. It is a table valued funtion and is added to SQL Server with Sql Server 2016 Version.
-- Space Separator
SELECT * from STRING_SPLIT('Middle & Advanced Level Sql Server Course', ' ')
-- and(&) Separator
SELECT * from STRING_SPLIT('Middle & Advanced Level Sql Server Course', '&')
-- dash(-) separator
SELECT * from STRING_SPLIT('Middle & Advanced Level - Sql Server Course', '-')
k. LEN
It shows the the character length of a string value.
SELECT LEN('Middle & Advanced Level Sql Server Course') as Result
Hope it will be useful.
I will continue with Data and Time functions in the next post.
You can find other blogpost list related with T-SQL below.
