SqlTutorial-1 : Lesson-11 : Class-5 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Built-in Sql functions: Mathematical functions

INDEX - 23 Methods

7.1. Trigonometric functions

• SIN(), COSE(), TAN(), ASIN(), ACOS(), ATAN()
• DEGREES(PI())

7.2. Filtering functions

• CEILING() and FLOOR()
• ROUND()
• ABS()

• POWER()
• SQUARE()
• SQRT()

7.5. Validating number.

• ISNUMERIC() -
• SIGN() - returns sing of given number

• RAND()

7.3. Logarithmic and Exponential functions

• EXP()
• LOG() & LOG10()
• PI() = 22/7

7.1. Trigonometric functions

Syntax

Any_function(float_expression);

Examples

1. Select ROUND(SIN(90),0) = Select ROUND(SIN(DEGREES(PI()/2),2) = 1
2. Select Cos(0) =>1
3. Select Tan(PI()/4) =>1
2. Degrees():

It converts a float value, which represents an angle in radians, into the value of this angle in degrees.
SELECT DEGREES(PI()/4) =>45

7.2. Filtering functions

1. CEILING():

Returns the value representing the smallest integer that is greater-than or equal to the input_number.

Syntax

CEILING ( input_float )

Examples:

1. Select CEILING(-1.25) = - 1
2. Select CEILING (1.25 = 2
2. FLOOR ():

Returns the value that represents the largest integer that is less-than or equal to the input_number.

Syntax

FLOOR ( input_number )

Examples:

1. SELECT 5-2*FLOOR(5/2) => 1
2. SELECT 1.22 - FLOOR(1.22) => .22 -- Returning the decimal portion
3. Select FLOOR(-1.25)=> -2
4. Select FLOOR(1.25) => 1
3. ROUND():

Rounds a value to the number of decimal places specified in the second parameter.

Syntax

ROUND ( numeric_expression, length [,integer_no ] )
Where
• Length: If +ve, numeric_expression is rounded to then umber of decimal positions specified by length. If –ve, numeric_expression is rounded on the left side of the decimal point.
• Integer_no: Is the type. Default is 0. If Non-zero is specified,numeric_expression is truncated.

Examples:

1. If length is negative and larger than the number of digits before the decimal point, then Round() returns Zero;

ROUND(748.58,-4) => 0
2. If length is negative number, Round() returns rounded numeric_expression, regardless of data type.

1. Select ROUND(748.58,-1) => 750.00
2. Select ROUND(748.58,-2) => 700.00
3. Select ROUND(748.58,-3) => 1000.00
4. Select ROUND(748.58,-4) => 0;
3. Using ROUND() to truncating

Select ROUND(150.75,0,1) =>  150.0
4. A numeric of anything but 0 will mean the number is truncated

1. SELECT ROUND(100.345678,5,0) =>100.345680
2. SELECT ROUND(100.345678,5,1) =>100.345670
3. SELECT ROUND(107.345678,-1,0) => 110.000000
4. SELECT ROUND(12.5,0) => 13.0
5. SELECT ROUND(12.4999,0) =>12.0000
6. SELECT ROUND(12.4999,1) =>12.5000
7. SELECT ROUND(12.4999,-1) =>10.0000
8. SELECT ROUND(3.22245, 3) => 3.22200

4. ABS():

Returns the absolute value of any number passed to it.

Syntax:

ABS (float_number);

Examples:

1. Returns positive value

Select ABS(-1.25) => 1.24

7.3. Logarithmic and Exponential functions

1. EXP():

EXP Returns a float, which is the exponential of the float value passed in as a parameter

Syntax

EXP (float_number);

Examples:

SELECT EXP(4) => 54.598150033144236
2. LOG():

It takes one positive valued parameter, and returns the natural logarithm of this parameter as a float value

Syntax

LOG(Decimal_Number)

Examples:

1. Find logramethic value

SELECT LOG(5.67) => 1.7351891177396608S
2. LOG value is reverse to Exponential

SELECT LOG(EXP(2)) => 2
3. LOG10():

Returns the base 10 logarithm of this parameter as a float value

Syntax

LOG10(float) ;

Examples:

1. Find the LOG10 value

SELECT LOG10(5.67) => 0.75358305889290655
4. PI() :

Returns the value of the mathematical constant PI to 16 decimal places

PI()

Examples:

1. Find the PI Value

SELECT PI()=>3.14159265358979315

7.4. Power and Root functions.

1. POWER(x,y) :

Returns a number(x) raised to the power of a number (y)

Syntax

POWER (float_x, int_n);

Examples

1. Find Square(10)

SELECT POWER(10,2)=>100
2. Square():

Returns the square of the given number

Syntax

SQUARE(float_number);

Examples

1. Find SQuare(2)

SELECT SQUARE(20)=> 400
2. SQRT(n) is reverse of SQUARE(n)

SELECT SQRT(SQUARE(2))=> 2
3. Sqrt():

Returns square root of the given number

Syntax

Sqrt(float_number);

Examples

1. Find the SquareRoot of 100

SELECT SQRT(100) => 10
2. Find the SQRT of decimal value

Select SQRT(125.43) => 11.199553562530964

7.5. Validating number.

1. ISNUMERIC():

Returns 1 if true otherwise 0

Syntax

ISNUMBERIC(String)

Examples

1. Validating negative decimal value

Select ISNUMERIC(-1.25)=> 1
2. Validating negative String Value

Select ISNUMERIC('SQL Server') => 0
3. Validating Date value

Select ISNUMERIC('2001-09-30') =>0
4. Validating Alphanumeric value

SELECT ISNUMERIC('123ABC') => 0
5. Validating numeric-String value

LECT ISNUMERIC('123')=>1
2. SIGN():

Returns sing of given number. i.e., -1 for negative +1 for positive and 0 for zero.

Syntax

SIGN(float_number);

Examples

1. Find sign of negative integer

SELECT SIGN(-23)=> -1

7.6. Generating Random Number

1. RAN():

Generates a random number between 0 and 1 as  float point value.

Syntax:

RAND ( [ seed ] )

Examples:

1. Generate floating number 0-1

SELECT RAND()=> 0.16134333864034361
2. Generate integer 1-100

SELECT CEILING(RAND()*100)=> 51

1. I have read your blog its very attractive and impressive. I like your blog.

Dot Net Online Training | Dot Net Online Training India

.Net Online Training | ASP.NET Online Training | WCF Online Training

2. This blog is very useful for Students , Learned a lot of new technonologies and thanks for sharing
.Net Online Training

3. Nice blog. Explained well. I have suggested to my friends to go through this blog. Very nice explanation. Thank you for sharing this useful information. keep sharing.If you want to learn Dotnet course in online, please visit below site.
dotnet Online Training, dotnet course, dotnet online training in kurnool, dotnet online training in hyderabad, dotnet online training in bangalore, online courses, online learning, online education, trending courses, best career courses

4. The information which you have provided is very good. It is very useful who is looking for salesforce Online Course Bangalore

5. Nice blog..! I really loved reading through this article. Thanks for sharing such
a amazing post with us and keep blogging...

ms dotnet training in hyderabad