6 Top Scalar functions in sql server

SQL Scalar functions

Following are the most useful scalar function in  SQL server and it return a single value

• UPPER()
• LOWER()
• SUBSTRING()
• LEN()
• ROUND()
• FORMAT()

UPPER () - Converts a input string to upper case

Example: - Below is “EMPLOYEE” table

Id	Name	Age
1	Alok Kumar Singh	30
2	Tomas Paul	45
3	Cristomfer Dee	85
4	Niel Macengi	55

Applying UPPER function on “Employee” table and getting below result.

SELECT Id, UPPER(Name) as EmployeeName, Age FROM Employee

Id	EmployeeName	Age
1	ALOK KUMAR SINGH	30
2	TOMAS PAUL	45
3	CRISTOMFER DEE	85
4	NIEL MACENGI	55

LOWER () - Converts a input string to lower case

Example: - Below is “EMPLOYEE” table

Id	EmployeeName	Age
1	ALOK KUMAR SINGH	30
2	TOMAS PAUL	45
3	CRISTOMFER DEE	85
4	NIEL MACENGI	55

Applying UPPER function on “Employee” table and getting below result.

SELECT Id, LOWER(Name) as EmployeeName, Age FROM Employee

Id	Name	Age
1	alok kumar singh	30
2	tomaspPaul	45
3	cristomfer dee	85
4	niel macengi	55

SUBSTRING () - Extract characters from a text field

Example: - Below is the simple example of SUBSTRIGN function

DECLARE @STRING VARCHAR(50)='EXAMPLE OF SUBSTRING FUNCTION'

SELECT SUBSTRING(@STRING, 1, 7)

This gives the Result: - EXAMPLE

LEN () - Returns the length of a input value

Example: - LEN() function used on above “Employee” table with below query

SELECT LEN(Name) as NameLength, Name FROM [SQLCLR].[dbo].[Employee]

Result:-

NameLength	Name
16	Alok Kumar Singh
10	Tomas Paul
14	Cristomfer Dee
12	Niel Macengi

ROUND () - Rounds a numeric value to the number of decimals specified

SELECT Employee.Name, ROUND(Salary.Amount,1) from Employee

JOIN Salary on Salary.EmployeeId=Employee.Id

GETDATE () - Returns the current system date and time

SELECT GETDATE()

FORMAT() - Formats how a field is to be displayed

SYNTAX:-  FORMAT ( Input_value, format, culture )

SELECT FORMAT(GETDATE(),'yyyy-MM-dd')