Conversion Functions

These are used for converting a value from one data type to other.There are 2 functions:

Convert (<Dtype>, <Exp>, [,style])

It converts the given expression into the specified data type.

Example

Convert (int, '100') (right)
Convert (int, '100A') (wrong)

The above statement will not execute because the given expression should be compatible of being converted into a newly given datatype.

Converting date into string

select Convert (varchar(20), Getdate ())   Answer Apr 17 2016 3:09PM

While storing the date in the database, we can store only in the default format i.e., mm/dd/yy. But we are provided with an option to retrieve the date in our own required format using the Convert function by specifying the optional parameter [style]. The value of the style between ranging between 100 to 114 or 1 to 14.

Example

select Convert (varchar(20), Getdate (),101) Answer 04/17/2016

Cast (<Exp> as <Dtype>)

It is similar to the convert function but much more descriptive. It doesn’t provide the Style option just like Convert.

Example

(1) select Cast (Getdate () as varchar (20))
(2) select Cast ('100' as int)
 

System Functions

System functions are as follows:

IsNumeric (Exp)

It returns '1' if the given Exp is a numeric value or else returns '0'.

Example

(1) select IsNumeric (100) Ans: 1
(2) select IsNumeric (100A) Ans: 0

IsDate (Exp)

It returns true if the given expression is in a valid date format.

Example

(1) select IsDate (‘10/24/80’) ans 1
(2) select IsDate (‘13/10/80’) ans 0

Example
 

select EId, Ename, Salary, Salary * 12 from Employee

NOTE

(1) It is allowed to perform mathematical calculations within the select statements.

(2) While performing Arithmetic operations if one value of the expression is a Null value, the result will be Null again because arithmetic operation on Null will be Null only.

IsNull (Exp1, Exp2)

If Exp1 is Not Null, it returns Exp1 only or else If Exp1 is Null it returns Exp2.

Example

(1) select IsNull (100, 200) Ans: 100
(2) select IsNull (Null,200) Ans: 200

SALARY + IsNull (Comm, 0)
5000 + IsNull (Null, 0) Ans: 5000
3500 + IsNull (500, 0) Ans: 4000

Select EID, Ename, Salary, Comm, Sal + IsNull (Comm, 0) from Employee

Coalese (Exp1 … Expn)

It is similar to the IsNull function which returns the first Not Null value.

Example

(1) select Coalese (Null, Null, 100, Null)  100
(2) select EID, Ename, Salary, Comm, Sal + Coalese (Comm, 0) from Employee

DataLength (Exp)

It returns the no. of bytes occupied by the given expression within the memory.

Example

Len ('Hello') ans 5 char
DataLength ('Hello') ans 5 bytes