ROUND() Function in sql server 2008

It is used to find the round value of numerical column.

EmployeeID	EmployeeName	EmployeeSalary
1	Mayank Dixit	5000.89
2	Mona	4899.59
3	Purnima	4000
4	Payal	8000

                                                            employee

In the above table (employee), we want to return round value of numericalfield (EmployeeSalary).

Syntax

SELECT        Column Name1, ROUND (Column Name2, 0) AS SubName

FROM           TableName

Note : '0' define no value after decimal. in place of '0' may be 1 or 2 or any numerical value which define how much numerical value will take place after decimal.

Example

SELECT        EmployeeName, ROUND (EmployeeSalary, 0) AS RoundEmployeeSalary

FROM           Employee

Here RoundEmployeeSalary is a subname (alias) of column.

Output:-

EmployeeID	EmployeeName	EmployeeSalary
1	Mayank Dixit	5001
2	Mona	4900
3	Purnima	4000
4	Payal	8000

LEN() Function in ms sql server 2008

It is used to find the length of name (text field as EmployeeName in table[employee]).it is also count space between textfields.
 

EmployeeID	EmployeeName
1	Mayank Dixit
2	Mona
3	Purnima
4	Payal

                                                     employee

In the above table (employee), we want to return value of textfield (employeename).

Syntax

SELECT        LEN(Column_Name) AS Name_of_the_expression

FROM            table_name;

Example

SELECT        LEN(EmployeeName) AS Length_of_employeename

FROM            Employee;

Here Length_of_employeename is a subname (alias) of column.

Output:-

Length_of_employeename

12

4

7

5

LCase() Lower Case Function In SQL Server

It is used to write all characters in small letter.

EmployeeID	EmployeeName
1	Mayank Dixit
2	Mona Dixit
3	Purnima
4	Payal

In the above table (employee), we want to write all characters (EmployeeName) in small letter.

Syntax

SELECT { fn LCASE(Column_Name) } AS Alias_Column_Name

FROM Table_Name;

Example

SELECT { fn LCASE(EmployeeName) } AS Name_OF_Employee

FROM Employee;

Here Name_OF_Employee is a subname (alias) of column.

Output:-

Name_OF_Employee

mayank dixit

mona dixit

purnima

payal

UCase() Upper Case function in SQL Server

It is used to write characters in capital letter.

EmployeeID	EmployeeName
1	Mayank Dixit
2	Mona Dixit
3	Purnima
4	Payal

In the above table (employee), we want to write all characters (EmployeeName) in capital letter.

Syntax

SELECT { fn UCASE(Column_Name) } AS Alias_Column_Name

FROM Table_Name;

Example

SELECT { fn UCASE(EmployeeName) } AS Name_OF_Employee

FROM Employee;

Here Name_OF_Employee is a subname (alias) of column.

Output:-

Name_OF_Employee

MAYANK DIXIT

MONA DIXIT

PURNIMA

PAYAL