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
SELECT FORMAT(GETDATE(),'yyyy-MM-dd')
No comments:
Post a Comment
Note: only a member of this blog may post a comment.