SPECIAL FUNCTIONS :-

db_name ( ) SQL Function:

  • Returns current name of the current database.
  • Select db_name ( )
  • Output → mydb

user_name( ) SQL Function:

  • Returns name of the user
  • Select user_name ( )
  •  Output → dbo {database owner}

host_name ( ) SQL Function:

  • Returns name of the server
  • Select host_name()
  • Output → IEEE

ident-current ( ) SQL Function:

  • This functions returns current value of the identity…………………..table name.
  • Select identity current(‘cust’).
  • Output →  103.

ident_seed ( ) SQL Function:

  • It returns starting value of the identity
  • Select ident_seed(‘cust’) 
  • Output → 100

ident_incr ( ) SQL Function:

  • It returns increment by value.
  • Select ident_incr(cust)
  • Output → 1

isnumeric ( ) SQL Function:

  • Isnumeric is expression,if expression is number this function returns ‘1’ otherwise ‘0’.
  • Select isnumeric(10)
  • Output → 1
  • Select isnumeric (‘abed’)
  • Output → 0

Isnull ( ) SQL Function:

  • This function is used to convert null values.

Syntax  :-   isnull(exp1,xp2)

  • If exp 1 is null ,it returns exp2
  • If exp1 is notnull ,it returns exp1 only

Example :-

  • Select is null (null,100)
  • Output → 100
  • Select isnull(200,100)
  • Output → 200

 

Example  :–

Select ename ,sal,comm,sal+isnull[comm,0]  Totsal from emp

Ename      sal          comm      total

 A           2000        NULL     2000

 B           5000        NULL     5000

 C           5000        1000        6000

Example  :-Select ename,sal,isnull(cast(comm as varchar),N/A) as comm from emp

A           2000       N/A

B            5000      N/A

C            5000     1000

Example  :-     Select sal,comm from emp  Order by comm.

(Note  :- By default sql server decide null value is low}

Display the commission in ascending order but null value is display to last only.

Select ename,sal,comm from emp Order by isnull(comm,99999999999)

            C         5000        1000

            A         2000        NULL

            B         5000        NULL