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