SQL Server Functions- Numeric Functions

Following are the SQL SERVER Numeric Functions.

ABS

- The ABS function returns the absolute value of the expression specified.

Syntax

ABS(number)

Example

select ABS(-542) as Absolute_Of_Number

Output

Absolute_Of_Number
542

AVG

- The AVG function returns the average value of the number.

Syntax

AVG(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select AVG(Experience) as Avrage_Experience

Output

Avrage_Experience
4

CEILING

- The CEILING function returns the smallest integer value that is greater than equal to the number specified in the expression.

Syntax

CEILING(number)

Example

select CEILING(23.24) as Ceiling_Number

Output

Ceiling_Number
24

COUNT

- The COUNT function returns the number of records in that particular table for expression specified. .

Syntax

Count(number)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select COUNT(Experience) as CountExperience

Output

CountExperience
4

FLOOR

- The FLOOR function is the opposite of CEILING function. IT returns the largest integer value that is less than equal to the number specified in the expression.

Syntax

FLOOR(number)

Example

select FLOOR(23.24) as floor_Number

Output

floor_Number
23

MAX

- The MAX function is used to obtain the maximum value of expression.

Syntax

MAX(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MAX(Experience) as Max_Experience

Output

Max_Experience
7

MIN

- The MIN function is used to obtain the minimum value of expression.

Syntax

MIN(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MIN(Experience) as Max_Experience

Output

Max_Experience
2

RAND

- The RAND function returns the random decimal number greater than 0 and less than 1.

Syntax

RAND(seed)

Example

select RAND() as Random_Number

Output

Random_Number
0.849042974736912

Note- The RAND function returns the any random value. The seed is optional. If seed is provided, it will give repeatable sequence of Random number every time.

ROUND

- The ROUND function returns the number rounded to certain number of decimal places.

Syntax

ROUND(number_to_round,decimal_places,operation)

Parameters

number_to_round- number to be rounded.
decimal_places- number of decimal places upto which number to be rounded.
operation- This is optional parameter. It can be 0 or 1. When specified as 0, it will round the result to the number of decima_places. Or if any value other than 0 is specified. It will truncate the result to the number of decimal_places. By defalut , value is 0.

Example

select ROUND(232.25,1) as Rounded_Number

Output

Rounded_Number
232.30

Example_2

select ROUND(232.25,1,1) as Rounded_Number

Output

Rounded_Number
232.20

SIGN

- The SIGN function returns the value indicating sign of the number.

Syntax

SIGN (number)

Example

select SIGN(25) as Sign_Of_Number

Output

Sign_Of_Number
1

If the number provided is >0 then returns 1 If the number provided is =0 then returns 0 If the number provided is <0 then returns -1

Example_2

select SIGN(-25) as Sign_Of_Number

Output

Sign_Of_Number
-1

SUM

- The SUM function returns the sum of the given expression.

Syntax

SUM(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select SUM(Experience) as Sum_Number

Output

Sum_Number
16

SQL Server Functions - String Functions

Following are the SQL server String Functions and their use in SQL.

ASCII

- The ASCII function returns the ASCII code for the particular/specified character.

Syntax

ASCII('character')

Example

SELECT ASCII('A') as Code_of_character

Output

Code_of_character
65

If more than one characters are specified, the ASCII functions will return the code for the first character of the word , other characters will be ignored.

Example

SELECT ASCII('Tech') as Code_of_character

Output

Code_of_character
84

CHAR

- The CHAR function is the opposite of ASCII function. It will return the Character of the specified code in the expression.

Syntax

CHAR('number')

Example

select char('65') as Character_Of_Code

Output

Character_of_code
A

CHARINDEX

- The CHARINDEX function returns the integer stating the location of a substring in a specified string.

Syntax

CHARINDEX(substring,string,start_position)

Parameters
substring-The substring to find in string.This is required.
string- The string to find in. This is required.
start_position- The is the position in string from the search will start- first position is 1. It is an optional parameter.


Example

select CHARINDEX('v','vishal') as Position_Of_Character

Output

Position_Of_Character
1

CONCAT

- The CONCAT function Concatenate two or more strings together.

Syntax

CONCAT(string_1,string_2,........string_n)

Parameters
string_1,string_2...string_n- strings to concat.

Example

select CONCAT('google','.com') as Concatenated_string

Output

Concatenated_string
google.com

DATALENGTH

- This function gives the length of an expression in bytes.

Syntax

DATALENGTH(expression)

Parameters
expression. string of which the length to calculate.

Example

select DATALENGTH('google') as Length_Of_String

Output

Length_Of_String
6

Note- DATALENGTH function also calculate the space.

Example

select DATALENGTH('google ') as Length_Of_String

Output

Length_Of_String
7

LEFT

- The LEFT function extracts a substring from the string starting from the left.

Syntax

LEFT(string,no_of_characters)

Parameters
string- strings from which the characters need to extract.
no_of_characters- no of characters to extract.

Example

select LEFT('google',2) as Extracted_String

Output

Extracted_String
go

Note- LEFT function also takes space into consideration if provided.

Example

select LEFT(' google',2) as Extracted_String

Output

Extracted_String
g

LEN

- The LEN function return the length of the string.

Syntax

LEN(expression)

Parameters
string - to calculate length.

Example

select LEN('google') as Length_Of_String

Output

Length_Of_String
6

Note- The LEN function does not count trailing spaces but it does count leading spaces in expression.

LOWER

- The LOWER function return the string into lowercase letter.

Syntax

LOWER (expression)

Parameters
string - to lowercase.

Example

select LOWER('GOOGLE') as Lowercase_String

Output

Lowercase_String
google

UPPER

- The UPPER function return the string into uppercase letter.

Syntax

UPPER (expression)

Parameters
string - to uppercase.

Example

select UPPER('google') as Uppercase_String

Output

Uppercase_String
GOOGLE

LTRIM

- The LTRIM function removes the leading spaces.

Syntax

LTRIM(expression)

Parameters
string - to remove leading spaces from.

Example

select LTRIM(' google' ) as removedleadingspace

Output

removedleadingspace
google

RTRIM

- The RTRIM function removes the trailing spaces.

Syntax

RTRIM(expression)

Parameters
string - to remove leading spaces from.

Example

select RTRIM('google ' ) as removedtrailingspace

Output

removedtrailingspace
google

PATINDEX

- The PATINDEX function returns the location of pattern in a string.

Syntax

PATINDEX (expression)

Parameters
pattern- the pattern to find. The pattern must be provided with wildcard characters
string- the string to search in.

Wildcard characters

%- matches string with any length including zero.
_ - matches single character.
[]- matches any characters provided in the bracket.
[^]- matches any characters which are not in the bracket.

Example

select PATINDEX('%goo%','google') as location_Of_Searched_Pattern

Output

location_Of_Searched_Pattern
google

Note- If supplied pattern not found , the function returns zero.

REPLACE

- The REPLACE function replaces the characters in the given string with another sequence of characters.

Syntax

REPLACE (string, string_to_replace,replacement_string)

Parameters
string - in which the characters to replace.
string_to_replace- the string to replace in string.
replacement_string- string to replace for string_to_replace.
Example

select REPLACE('KGS','K','S') as replaced_string

Output

replaced_string
SGS

RIGHT

- The RIGHT function return the substring from a string starting from the right.

Syntax

RIGHT(string,no_of_characters)

Parameters
string - the string from which the characters need to extract from .
no_of_characters - number characters to extract.

Example

select RIGHT('google',3) as Extracted_From_Right

Output

Extracted_From_Right
gle

SPACE

- The SPACE function returns the string with given spaces.

Syntax

SPACE(number)

Parameters
number- number of spaces to return.

Example

select SPACE(10) as SpaceReturn

Output

SpaceReturn

STR

- The STR function returns the string representation of the number.

Syntax

STR(number,length,decimal_places)

Parameters
number- number to convert to string.
length- the length of the returned string. Default length is 10
decimal_places- the number of decimals to display.

Example

select STR(54,4,1) as returnednumber

Output

returnednumber
54.0

STUFF

- The STUFF function deletes given number of characters from the string and inserts another characters at the position starting from the given position.

Syntax

STUFF(source_string,start,length,another_string)

Parameters
source_string- the string to modify.
start- the position in source string from which to delete characters and start inserting another characters.
length- the number of character to delete from source string.
another_string- the string to insert in place of deleted characters.

Example

select STUFF('DJJEE',1,3,'ggg') as returnedstring

Output

returnedstring
gggEE

SUBSTRING

- The SUBSTRING function extract substring from given string.

Syntax

SUBSTRING(source_string,start,number_of_characters)

Parameters
source_string- the string to modify.
start- the position to start from.
number_of_characters- the number of character to extract.

Example

select SUBSTRING('google',1,3) as returnedstring

Output

returnedstring
goo