Numeric Function in MySql

There are two catagory in Numeric Functions.
1-Airthmetic Operator
2-Mathematical Function
 
////////////////////////////Mathematical Function////////////////////
NameDescription
ABS()Return the absolute value
ACOS()Return the arc cosine
ASIN()Return the arc sine
ATAN2(), ATAN()
Return the arc tangent of the two arguments
ATAN()Return the arc tangent
CEIL()Return the smallest integer value not less than the argument
CEILING()Return the smallest integer value not less than the argument
CONV()Convert numbers between different number bases
COS()Return the cosine
COT()Return the cotangent
CRC32()(v4.1.0)Compute a cyclic redundancy check value
DEGREES()Convert radians to degrees
EXP()Raise to the power of
FLOOR()Return the largest integer value not greater than the argument
LN()Return the natural logarithm of the argument
LOG10()Return the base-10 logarithm of the argument
LOG2()Return the base-2 logarithm of the argument
LOG()Return the natural logarithm of the first argument
MOD()Return the remainder
OCT()Return an octal representation of a decimal number
PI()Return the value of pi
POW()Return the argument raised to the specified power
POWER()Return the argument raised to the specified power
RADIANS()Return argument converted to radians
RAND()Return a random floating-point value
ROUND()Round the argument
SIGN()Return the sign of the argument
SIN()Return the sine of the argument
SQRT()Return the square root of the argument
TAN()Return the tangent of the argument
TRUNCATE()Truncate to specified number of decimal places

1-ABS()
mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32
2-ACOS()
mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949


3-ASIN()


mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');



4-ATAN()


mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941
5-CEIL() 


->CEIL is the synonym of CELLING


mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1



6-COS()
mysql> SELECT COS(PI());
        -> -1
7-COT()
mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> NULL

8-CRC2()
->Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.
mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538
9-DEGREES()
mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90
10-EXP()
mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);
        -> 0.13533528323661
mysql> SELECT EXP(0);
        -> 1
11-FLOOR()
mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2
12-LOG()
mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL

13-LOG2()
mysql> SELECT LOG2(65536);
        -> 16
mysql> SELECT LOG2(-100);
        -> NULL



14-LOD10()


mysql> SELECT LOG10(2);
        -> 0.30102999566398
mysql> SELECT LOG10(100);
        -> 2



14-MOD()


mysql> SELECT MOD(29,9);
        -> 2



15-OCT()


mysql> SELECT OCT(12);
        -> '14'
16-POW()
mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25
17-RADIANS()
mysql> SELECT RADIANS(90);
        -> 1.5707963267949
18-ROUND()
mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20

19-RAND()
mysql> SELECT RAND();
        ->58538189584891
20-SIGN()
Returns the sign of the argument as -1, 0, or 1, depending on whether 
X is negative, zero, or positive. 


mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1


 


21-SQRT()


mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL



22-TRUNCATE()


mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028



 


 


 


 


 


///////////////////AIRTHMATIC OPERATOR////////////////////






DIV(v4.1.0)Integer division


/Division operator


-Minus operator


%Modulo operator


+Addition operator


*Times operator


-Change the sign of the argument




  • 
+ 

    

Addition: 
    

    mysql> SELECT 3+5;
        -> 8

    

    • 

  • 
- 

    

Subtraction: 
    

    mysql> SELECT 3-5;
        -> -2

    

    • 

  • 
- 
    

Unary minus. This operator changes the sign of the argument. 
    

    mysql> SELECT - 2;
        -> -2
* 

    

Multiplication: 
    

    mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
    

    

    • 

    / 
    Division: 
    mysql> SELECT 3/5;
        -> 0.60

    Division by zero produces a NULL result:
    mysql> SELECT 102/(1-1);        
                    -> NULL
     
    (OR)
    mysql> SELECT 5 DIV 2;
        -> 2

    Comments

    Post a Comment

    Popular posts from this blog

    Error : DependencyManagement.dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: com.adobe.aem:uber-jar:jar:apis -> version 6.3.0 vs 6.4.0

    Image
    I got above error after adding dependency in Maven POM xml file placed under newly created AEM arch type and running command  mvn -PautoInstallPackage clean install . Error : DependencyManagement.dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: com.adobe.aem:uber-jar:jar:apis -> version 6.3.0 vs 6.4.0 After google and gone through few article I got command ( mvn -PautoInstallPackage -Padobe-public clean install) . Once I run this command this issue with jar compatibility was resolved. Happy Learning!!!
    Read more

    Operators in Asterisk with Linux

    Operators When you create an Asterisk dialplan, you’re really writing code in a specialized scripting language. This means that the Asterisk dialplan—like any programming language—recognizes symbols called operators that allow you to manipulate variables. Let’s look at the types of operators that are available in Asterisk: Boolean operators These operators evaluate the “truth” of a statement. In computing terms, that essentially refers to whether the statement is something or nothing (non-zero or zero, true or false, on or off, and so on). The Boolean operators are: expr1 | expr2 This operator (called the “or” operator, or “pipe”) returns the evaluation of expr1 if it is true (neither an empty string nor zero). Otherwise, it returns the evaluation of expr2. expr1 & expr2 This operator (called “and”) returns the evaluation of expr1 if both expressions are true (i.e., neither expression evaluates to an empty string or zero). Otherwise, it returns zero. expr1 {=,
    Read more

    ERROR Exception while handling event Sitecore.Eventing.Remote.PublishEndRemoteEventException: System.AggregateExceptionMessage: One or more exceptions occurred while processing the subscribers to the 'publish:end:remote'

    I was getting below error while working with cache management on CM and CD server. Actually my code was working fine on CM server but I was not able to access or clear cache object on CD server because of wrong event selection.  PID[18108] Error       ManagedPoolThread #3 16:24:16 ERROR Exception while handling event Sitecore.Eventing.Remote.PublishEndRemoteEventException: System.AggregateExceptionMessage: One or more exceptions occurred while processing the subscribers to the 'publish:end:remote' event.Source: Sitecore.Kernelat Sitecore.Events.Event.EventSubscribers.RaiseEvent(String eventName, Object[] parameters, EventResult result)at Sitecore.Events.Event.RaiseEvent(String eventName, Object[] parameters)at Sitecore.Eventing.Subscription`1.Invoke(Object instance, EventContext context)at Sitecore.Eventing.EventProvider.RaiseEvent(Object event, Type eventType, EventContext context) Above error comes due to wrong event args access in CD server. There are two event args in
    Read more