SQL Server 2005 provides built-in functions that can be used to perform certain operations. Functions can be used or included in the following:

- The select list of a query that uses a SELECT statement to return a value.

- A WHERE clauses search condition of a SELECT statement to limit the rows that qualify for the query.

Syntax for executing a function:

SELECT <Fun Name> ( [ <expressions> ] )

-The expression can be a constant values or a name of a column.

Functions can be classified into 2 types:

Single Row Functions
Group Functions

A single row function executes once for each row that is present in the table where as group functions take multiple rows into consideration and returns a single value as output.

Single Row Function Categories:

Mathematical Functions
String Functions
Date and Time Functions
System Functions

FEBRUARY 7, 2011 BY DILIP KUMAR JENA

SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – Mathematical Functions

These functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value; they take “n” as input where n is a numeric expression.

ABS (n): A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Select ABS(10) Ouput: 10

Select ABS(-10) Ouput: 10

CEILING (n): Returns the smallest integer greater than, or equal to, the specified numeric expression.

SELECT CEILING(15.6) OUTPUT: 16

SELECT CEILING(15.6) OUTPUT: -15

CEILING (n): Returns the largest integer less than or equal to the specified numeric expression.

SELECT FLOOR(15.6) OUTPUT: 15

SELECT FLOOR(15.6) OUTPUT: -16

LOG (n): Returns the natural logarithm of the specified expression, i.e. base-e

SELECT LOG(10) OUTPUT: 2.30258509299405

LOG10 (n): Returns base-10 logarithm of the specified expression, i.e. base e

SELECT LOG10(10) OUTPUT: 1

PI(): Returns the constant value of PI.

SELECT PI() OUTPUT: 3.14159265358979

POWER(n, m): Returns the value of the specified expression n to the specified power m.

SELECT POWER(10, 3) OUTPUT: 1000

RAND ( [SEED] ): Returns a random float value from 0 through 1.

- SEED: Is an integer expression that gives the seed value. If seed is not specified, the Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

SELECT RAND() -Each time we execute we get a random value.

SELECT RAND(100) -Each time we execute we get the same value.

ROUND ( n , length [ ,function ] ): Returns a numeric expression, rounded to the specified length or precision.

SELECT ROUND(156.567, 2) OUTPUT: 156.57

SELECT ROUND(156.567, 1) OUTPUT: 156.6

SELECT ROUND(156.567, 0) OUTPUT: 157

-If the seed is positive rounding will be done after the decimal, if it is negative rounding will be done before the decimal:

SELECT ROUND(156.567, -1) OUTPUT: 160

SELECT ROUND(156.567, -2) OUTPUT: 200

-If we specify the optional parameter function that is an integer value we can decide to truncate the value or round the value. If it is 0 (default) rounds the value and value greater than 0 truncates the value.

SELECT ROUND(156.567, 2, 1) OUTPUT: 156.56

SELECT ROUND(156.567, -2, 1) OUTPUT: 100

SIGN(n): Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

- If n<0 it returns -1

- If n=0 it returns 0

- If n>0 it returns 1

SELECT SIGN(-100) OUTPUT: -1

SELECT SIGN(0) OUTPUT: 0

SELECT SIGN(100) OUTPUT: 1

SQRT(n): Returns the square root of the specified expression.

SELECT SQRT(81) OUTPUT: 9

SELECT SQRT(30) OUTPUT: 5.47722557505166

SQUARE(n): Returns the square of the specified expression.

SELECT SQUARE(35) OUTPUT: 1225

-Apart from the above it provides with trigonometric function like COS, COT, SIN, TAN, ACOS, ASIN, ATAN for which we need to provide the degrees.

String Functions

These functions perform an operation on a string input value and return a string or numeric value.

ASCII(s): Returns the ASCII code value of the leftmost character of the expression.
ASCII(‘A’) OUTPUT: 65
ASCII(‘BCD’) OUTPUT: 66

CHAR(n): Converts the given ASCII code to a character.
CHAR(97) OUTPUT: a

NCHAR(n): Returns the Unicode character with the specified integer code ranging between 0 to 65, 535, as defined by the Unicode standard.
CHAR(300) OUTPUT: Ĭ

CHARINDEX(search exp, string exp [ , start_location ] ): Returns the starting position of the search exp in the string exp which can also be a column name.
CHARINDEX(‘O’, ‘HELLO WORLD’) OUTPUT: 5
-In this case it returns 5 as output because it starts its search from the beginning of the string, we can change it by using the start location optional parameter.
CHARINDEX(‘O’, ‘HELLO WORLD’, 6) OUTPUT: 8
-WAQ to get the details of employees whose name contains the character ‘M’ in it.
Sol: SELECT * FROM EMP WHERE CHARINDEX(‘M’, ENAME)>0

LEFT(s, n): Returns the left part of the string with the specified number of characters.
SELECT LEFT(‘HELLO’, 3) OUTPUT: HEL
-WAQ to get the details of employees whose name contains the first 2 characters as ‘VE’.
Sol: SELECT * FROM EMP WHERE LEFT(ENAME, 2)=’VE’

RIGHT(s, n): Returns the right part of the string with the specified number of characters.
SELECT RIGHT(‘HELLO’, 3) OUTPUT: LLO
-WAQ to get the details of employees whose name ends with characters ‘TT’.
Sol: SELECT * FROM EMP WHERE RIGHT(ENAME, 2)=’TT’

SUBSTRING(s, start, length): Returns a part of a string from string s starting from start position, where length is the no of chars to be picked.
SELECT SUBSTRING(‘HELLO’, 1, 3)  OUTPUT: HEL
SELECT SUBSTRING(‘HELLO’, 3, 3)  OUTPUT: LLO
SELECT SUBSTRING(‘HELLO’, 2, 3)  OUTPUT: ELL
-WAQ to get the details of employees whose names 3rd and 4th characters are ‘TI’.
Sol: SELECT * FROM EMP WHERE RIGHT(LEFT(ENAME, 4), 2)=’TI’
Sol: SELECT * FROM EMP WHERE SUBSTRING(ENAME, 3, 2)=’TI’

LEN(s): Returns the number of characters of the specified string expression, excluding trailing blanks.
SELECT LEN(‘HELLO’)    OUTPUT: 5
SELECT LEN(‘   HELLO’)    OUTPUT: 8
-WAQ to get the details of employees whose names was 5 characters in length
Sol: SELECT * FROM EMP WHERE LEN(ENAME)=5
SELECT LEN(‘HELLO   ‘)    OUTPUT: 5

LOWER(s): Returns a character expression after converting the given character data to lowercase.
SELECT LOWER(‘Hello’) OUTPUT: hello

UPPER(s): Returns a character expression after converting the given character data to uppercase.
SELECT UPPER(‘Hello’) OUTPUT: HELLO

LTRIM(s): Returns a character expression after it removes leading blanks.
SELECT LEN(LTRIM(‘ HELLO’)) OUTPUT: 5
SELECT ‘HELLO ‘ + LTRIM(‘ WORLD’) OUTPUT: HELLO WORLD

RTRIM(s): Returns a character expression after it removes trailing blanks.
SELECT RTRIM(‘HELLO ‘) + ‘ WORLD’ OUTPUT: HELLO WORLD

REPLACE(s1, s2, s3): Replaces all occurrences of the s2 in s1 with s3.
SELECT REPLACE(‘HELLO’, ‘L’, ‘X’) OUTPUT: HEXXO

REPLICATE(s, n): Repeats the expression ‘s’ for specified ‘n’ number of times.
SELECT REPLICATE(‘HEL’, 2) OUTPUT: HELHEL

REVERSE(s): Returns the reverse of the given string ‘s’.
SELECT REVERSE(‘HELLO’) OUTPUT: OLLEH

SOUNDEX(s): Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of strings and the second through fourth characters of the code are numbers.
SELECT SOUNDEX (‘Smith’), SOUNDEX (‘Smyth’)
-Generally we use then when we perform comparison of words, which are sounded in the same way but have different spelling like color & colour. Suppose in a table the ename of a person is smith we will get the result even if the statement is written as following:
SELECT * FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)

DIFFERENCE(S1, S2): Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.
SELECT SOUNDEX(‘SMITH’), SOUNDEX(‘SMYTH’),
DIFFERENCE(‘SMITH’,'SMYTH’)

SPACE(n): Returns a string with specified ‘n’ number of repeated spaces.
SELECT ‘HELLO’ + SPACE(1) + ‘WORLD’ OUTPUT: HELLO WORLD

STUFF(s, start, length, replace_str): Replaces specified length of characters from specified starting point with replace_str in the string ‘s’
SELECT STUFF(‘ABXXCDXX’, 3, 3, ‘YY’) OUTPUT: ABYYDXX

Date and Time Functions

The following functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

GETDATE(): Returns the current date and time of the server in SQL Server standard internal format.

SELECT GETDATE()

DAY(date): Returns an integer representing the DAY of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT DAY(GETDATE())

SELECT DAY(‘10/24/78’) OUTPUT: 24

MONTH(date): Returns an integer representing the MONTH of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT MONTH(GETDATE())

SELECT MONTH(‘10/24/78’) OUTPUT: 10

YEAR(date): Returns an integer representing the YEAR of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

SELECT YEAR(GETDATE())

SELECT YEAR(‘10/24/78’) OUTPUT: 1978

DATENAME(datepart, date): Returns a character string representing the specified datepart of the specified date, datepart is the parameter that specifies the part of the date to return. The following table lists dateparts and abbreviations recognized by Sql Server:

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

SELECT DATENAME(mm, ‘10/24/78’) OUTPUT: October

SELECT DATENAME(dd, ‘10/24/78’) OUTPUT: 10

DATEPART(datepart, date): This is same as DATENAME function but the only difference isweekday (dw) of DATEPART function returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7, where as in the case of DATENAME returns the value in string format that is Sunday, Monday, … Saturday.

DATEADD(datepart, number, date): Returns a new datetime value based on adding an interval to the specified date, datepart is the value that has to be added and number is the interval.

SELECT DATEADD(dd, 30, GETDATE()) –Adds 30 days to GETDATE().

SELECT DATEADD(mm, 16, GETDATE()) –Adds 16 months to GETDATE().

DATEDIFF(datepart, startdate, enddate): Returns the difference between the start and end dates in the give datepart format.

SELECT DATEDIFF(yy, ‘10/24/78’, GETDATE())

GETUTCDATE()-Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time).

SELECT GETUTCDATE()

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

…………………………

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

…………………………

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’

END) AS COMMENTS FROM EMP

Set Operators:

COUNT(expression): Returns the number of items in a group.

SELECT COUNT(*) FROM EMP

SELECT COUNT(*) FROM EMP WHERE DEPTNO=20

SELECT COUNT(COMM) FROM EMP

COUNT_BIG(expression): COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

SELECT COUNT_BIG(*) FROM EMP

SUM(expression): Returns the sum of all the values. SUM can be used with numeric columns only. Null values are ignored

SELECT SUM(SAL) FROM EMP

AVG(expression): Returns the average of the values in a group. Null values are ignored.

SELECT AVG(SAL) FROM EMP

MAX(expression): Returns the maximum value in the expression.

SELECT MAX(SAL) FROM EMP

MIN(expression): Returns the minimum value in the expression.

SELECT MIN(SAL) FROM EMP

STDEV(expression): Returns the statistical standard deviation of all values in the specified expression.

SELECT STDEV(SAL) FROM EMP

SIVANANDA REDDY GANGIREDDY

Pages

Tuesday, October 22, 2013