Advertisement

Latest Post

Saturday, 1 October 2016

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')



Government Jobs