SQL server provide number of built in functions like mathematical functions, character functions, date and time functions, aggregative functions,convertion functions etc.these can be used to perform certain operations and return a value.
Syntax: SELECT <Function Name> [Expressions]
Mathematical Functions: These functions perform a calculation based on input values provided as arguments, and return a numeric value.
ABS (): Returns the absolute, positive value of the given numeric expression.
Ex: select ABS(-15)---- 15 select ABS(45)----- 45
CEILING (): Returns the smallest integer greater than, or equal to, the given numeric expression.
Ex: select ceiling(15.000)----15 select ceiling(15.0001)----16 select ceiling(-12.34)-----(-12)
FLOOR (): Returns the largest integer less than or equal to the given numeric expression.
Ex: select floor(15.000)---15 select floor(15.0001)----15 select floor(-12.34)----(-13)
SQUARE (): Returns the square of the given expression.
Ex: select SQUARE(5)---25
SQRT (): Returns the square root of the given expression.
Ex: select SQUARE(25)---5
SIGN (): Returns the positive (+1), zero (0), or negative (-1) sign of the given expression.
Ex: select SIGN(42)------------1 select SIGN(0)-------------0 select SIGN(-42)-----------(-1)
PI (): Returns the constant value of PI.
Ex: select PI()---------3.14159265358979
LOG (): Returns the natural logarithm of the given expression.
Ex: select LOG(2)------ 0.693147180559945
LOG 10(): Returns the base-10 logarithm of the given expression.
Ex: select LOG10(10)----1
SIN (): Returns the trigonometric sine of the given angle (in radians) in an approximate numeric expression.
Ex: select SIN (0) -------0
COS (): A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression.
Ex: select COS (0) -------1
TAN (): Returns the tangent of the input expression.
Ex: select TAN (0) ---------0
String Functions: These functions perform an operation on a string input value and return a string or numeric value.
ASCII (): Returns the ASCII code value of the leftmost character of a character expression.
Ex: Select ASCII (‘Z’) -----90
CHAR (): A string function that converts an int ASCII code to a character.
Ex: Select CHAR (90) -----Z
CHARINDEX (): Returns the starting position of the specified expression in a character string.
Ex: Select CHARINDEX (‘S’,’SUDHAKAR’) -------1
LEFT (): Returns the left part of a character string with the specified number of characters.
Ex: Select LEFT (‘SUDHAKAR’, 5) ----SUDHA
RIGHT (): Returns the right part of a character string with the specified number of characters.
Ex: Select RIGHT (‘SUDHAKAR’, 3) ------KAR
LEN (): Returns the number of characters, rather than the number of bytes, of the given string expression.
Ex: Select LEN (‘WELCOME’) ------------7
LOWER (): Returns a character expression after converting uppercase character data to lowercase.
Ex: Select LOWER (‘SAI’) --------sai
UPPER (): Returns a character expression with lowercase character data converted to uppercase.
Ex: Select UPPER (‘sai’) ------SAI
LTRIM (): Returns a character expression after removing leading blanks.
Ex: Select LTRIM (‘ HELLO’) --------HELLO
RTRIM (): Returns a character string after truncating all trailing blanks.
Ex: Select RTRIM (‘HELLO ‘) -------HELLO
REPLACE (): Replaces all occurrences of the second given string expression in the first string expression with a third expression.
Ex: Select REPLACE (‘JACK AND JUE’, ‘J’, ‘BL’) ------BLACK AND BLUE
REPLICATE (): Repeats a character expression for a specified number of times.
Ex: Select REPLICATE (‘SAI’, 3) -------SAISAISAI
REVERSE (): Returns the reverse of a character expression.
Ex: Select REVERSE (‘HELLO’) --------OLLEH
SPACE (): Returns a string of repeated spaces.
Ex: Select (‘SAI’+SPACE (50) +’SUDHAKAR’) -----SAI SUDHAKAR
0 comments:
Post a Comment