System Functions:
ISNUMERIC( expression ):
Determines whether an expression is a valid numeric type. If it is numeric it
returns 1 else return 0.
SELECT
ISNUMERIC(100) OUTPUT:
1
SELECT ISNUMERIC(‘100’) OUTPUT: 1
SELECT
ISNUMERIC(‘100A’) OUTPUT:
0
ISDATE (expression): Determines
whether an input expression is a valid date or not. If it is a valid date it
returns 1 else return 0. Valid date in the sense the expression, which is
present in mm/dd/yy format.
SELECT ISDATE('12/21/98') OUTPUT: 1
SELECT ISDATE('21/12/98') OUTPUT: 0
ISNULL (expression1,
expression2): if expression1 is null then it returns expression2.
SELECT
ISNULL(100, 200) OUTPUT:
100
SELECT
ISNULL(NULL, 200) OUTPUT:
200
SELECT EMPNO,
ENAME, SAL, COMM, SAL + COMM AS [TOTAL SAL] FROM EMP
-In above case if any of the
value in the comm. Is null it returns null in the Total Sal because any
arithmetic operations performed on a null value results to null only at this
time the statement has to be written as following:
SELECT EMPNO,
ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS [TOTAL SAL] FROM EMP
COALESCE (expression1,
expression2, …… expression n): Returns
the first not null expression in the list of expressions given, similar to
isnull but we can give multiple values here.
SELECT
COALESCE(NULL, 100, NULL, 200) OUTPUT:
100
SELECT EMPNO,
ENAME, SAL, COMM, SAL + COALESCE(COMM, 0) AS [TOTAL SAL] FROM EMP
DATALENGTH (expression) : Returns
the number of bytes used to represent any expression.
SELECT
DATALENGTH(100) OUTPUT:
4
SELECT
DATALENGTH(‘HELLO’) OUTPUT:
5
HOST_NAME(): Returns the name of
the workstation.
SELECT
HOST_NAME()
IDENT_CURRENT('table_name'):
Returns the last identity value generated for a specified table by the identity
function.
SELECT
IDENT_CURRENT(‘BANK’)
IDENT_SEED('table_name'): Returns
the seed value that was specified when the identity function in a table was
created.
SELECT
IDENT_SEED(‘BANK’)
IDENT_INCR('table_name'): Returns
the increment value that was specified when the identity function in a table
was created.
SELECT
IDENT_INCR(‘BANK’)
NEWID( ): Creates a unique value
of type uniqueidentifier.
SELECT
NEWID()
NULLIF(expression1, expression2):
Returns the first expression if the two expressions are not equivalent. If the
expressions are equivalent, returns a null value.
SELECT
NULLIF(100, 200) OUTPUT:
100
SELECT
NULLIF(100, 100) OUTPUT:
NULL
ROWCOUNT_BIG(): Returns the
number of rows affected by the last statement executed. If we use this after a
select statement it will return us the number of rows the select statement has
returned.
SELECT
* FROM EMP
SELECT
ROWCOUNT_BIG FROM EMP
APP_NAME(): Returns the name of
the application from where the statement is executed.
SELECT
APP_NAME()
CASE: Evaluates a list of
conditions and returns one of multiple possible result expressions. It has two
formats:
-The simple CASE function
compares an expression to a set of simple expressions to determine the result.
-The searched CASE function
evaluates a set of Boolean expressions to determine the result.
- Both formats support an
optional ELSE argument.
CASE
<expression>
WHEN
when_expression THEN result_expression
WHEN when_expression THEN
result_expression
…………………………
ELSE
else_result_expression
END
-In this case if the expression
matches with any of the when_expression it returns the corresponding
result_expression, if it does not match with any then it returns
else_result_exression.
SELECT
EMPNO, ENAME, SAL, JOB,
(CASE JOB
WHEN ‘PRESIDENT’
THEN ‘BIG BOSS’
WHEN ‘MANAGER’
THEN ‘BOSS’
WHEN ‘ANALYST’
THEN ‘SCIENTIST’
ELSE ‘EMPLOYEE’
END) AS COMMENTS
FROM EMP
SELECT
EMPNO, ENAME, JOB, SAL,
(CASE
SIGN(SAL-3000)
WHEN
1 THEN ‘ABOVE TARGET’
WHEN
0 THEN ‘ON TARGET’
WHEN
–1 THEN ‘BELOW TARGET’
END)
AS COMMENTS FROM EMP
-The above statement can be
written in one more way also by using the second format of the CASE function.
CASE
WHEN
condition THEN result_expression
WHEN
condition THEN result_expression
…………………………
ELSE
else_result_expression
END
SELECT
EMPNO, ENAME, JOB, SAL,
(CASE
WHEN
SAL>3000 THEN ‘ABOVE TARGET’
WHEN
SAL=3000 THEN ‘ON TARGET’
WHEN
SAL<3000 THEN ‘BELOW TARGET’
No comments:
Post a Comment