# SQL Mathematical Functions, String Functions for Programmers

## Preface

Functions in MySQL include mathematical functions, string functions, date and time functions, conditional judgement functions, system information functions, and so on. The functions and usages of these functions in MySQL are described below.

## 1. Mathematical Functions

MySQL supports a variety of mathematical types of functions, including absolute value functions, random functions, and so on.The following table lists the array functions commonly used in MySQL.

Function Name interpretative statement
ABS (num) Returns the absolute value of num
CEIL(num) Returns the smallest integer value greater than num
FLOOR(num) Returns the largest positive integer less than num
MOD(num1,num2) Returns the value of num1% num2
RAND() Returns a random number of 0-1
TRUNCATE(num1,num2) Returns num1 truncated num2 decimal places
ROUND(num1,num2) Returns the value of the parameter num1 rounded to a num2 decimal
POW(x,y) Returns the y-th power of x

### 1.1 ABS

The following shows the results of using the absolute value function for positive and negative numbers.

```mysql> mysql> select abs(-1.2),abs(1.2);
+-----------+----------+
| abs(-1.2) | abs(1.2) |
+-----------+----------+
|       1.2 |      1.2 |
+-----------+----------+
1 row in set (0.00 sec)
```

### 1.2 CEIL

CEIL(X) returns a smallest integer greater than x

```mysql> select ceil(1.6),ceil(1.2),ceil(-1.2);
+-----------+-----------+------------+
| ceil(1.6) | ceil(1.2) | ceil(-1.2) |
+-----------+-----------+------------+
|         2 |         2 |         -1 |
+-----------+-----------+------------+
1 row in set (0.00 sec)
```

### 1.3 FLOOR

FLOOR(x) returns a maximum integer smaller than x

```mysql> select FLOOR(1.8),FLOOR(-1.8);
+------------+-------------+
| FLOOR(1.8) | FLOOR(-1.8) |
+------------+-------------+
|          1 |          -2 |
+------------+-------------+
1 row in set (0.00 sec)
```

### 1.4 MOD

MOD(x,y) returns the module of x%y

```mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
```

### 1.5 RAND

RAND() returns a random number of 0-1

```mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8903573117129107 |
+--------------------+
1 row in set (0.00 sec)
```

### 1.6 ROUND

round(x,y) returns the rounded y-bit tail of parameter x

```mysql> select round(1.23,2),round(1.67,2),round(1.44,2);
+---------------+---------------+---------------+
| round(1.23,2) | round(1.67,2) | round(1.44,2) |
+---------------+---------------+---------------+
|          1.23 |          1.67 |          1.44 |
+---------------+---------------+---------------+
1 row in set (0.00 sec)
```

### 1.7 TRUNCATE

TRUNCATE(x,y) returns the result where x is truncated to a y-bit decimal

```mysql> select round(1.23,2),round(1.67,2),round(1.44,2),truncate(1.23,2),truncate(1.67,2),truncate(1.44,2);
+---------------+---------------+---------------+------------------+------------------+------------------+
| round(1.23,2) | round(1.67,2) | round(1.44,2) | truncate(1.23,2) | truncate(1.67,2) | truncate(1.44,2) |
+---------------+---------------+---------------+------------------+------------------+------------------+
|          1.23 |          1.67 |          1.44 |             1.23 |             1.67 |             1.44 |
+---------------+---------------+---------------+------------------+------------------+------------------+
1 row in set (0.00 sec)
```

### 1.8 POW

POW(x,y) returns the y-th power of X

```mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
```

MySQL also supports trigonometric (sine, cosine, tangent, cotangent) and logarithmic functions. Normally, there are few scenarios where mathematical functions are used in daily development. Usually, we store them in MySQL after processing the relevant mathematical calculations in Java programs.

## 2. String Functions

String functions are the most common functions in MySQL. The following list of commonly used string functions:

### 2.1 CHAR_LENGTH and LENGTH

```mysql> select char_length('abc');
+--------------------+
| char_length('abc') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)
mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
```

LENGTH(str) returns string byte length

```mysql> select length('I');
+---------------+
| length('I')  |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
mysql> select char_length('I');
+--------------------+
| char_length('I')  |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)
```

### 2.2 CONCAT

CONCAT(s1,s2...) Splice the incoming string, note that any string spliced with NULL returns NULL

```mysql> select concat(1,'a'),concat(null,'a');
+---------------+------------------+
| concat(1,'a') | concat(null,'a') |
+---------------+------------------+
| 1a            | NULL             |
+---------------+------------------+
1 row in set (0.00 sec)
```

### 2.3 INSERT

INSERT(str,x,y,str1) inserts STR1 at the x position of the STR and ends at the Y position

```mysql> select insert('abc',1,2,'haha123');
+-----------------------------+
| insert('abc',1,2,'haha123') |
+-----------------------------+
| haha123c                    |
+-----------------------------+
1 row in set (0.00 sec)
```

### 2.4 LOWER and UPPER

LOWER(str), UPPER(str) functions: Convert a string to lowercase or uppercase:

```mysql> select UPPER('abc'),LOWER('ABC');
+--------------+--------------+
| UPPER('abc') | LOWER('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)
```

### 2.5 LEFT and RIGHT

The LEFT(str,x) and RIGHT(str,x) functions return the leftmost or rightmost x characters of a string, respectively

```mysql> select LEFT('i love china,because i am a chinese',12),RIGHT('i love china,because i am a chinese',15);
+------------------------------------------------+-------------------------------------------------+
| LEFT('i love china,because i am a chinese',12) | RIGHT('i love china,because i am a chinese',15) |
+------------------------------------------------+-------------------------------------------------+
| i love china                                   |  i am a chinese                                 |
+------------------------------------------------+-------------------------------------------------+
1 row in set (0.00 sec)
```

### 2.6 REPLACE

REPLACE(str,s1,s2) replaces the character sequence containing S1 in str with S2

```mysql> select replace('abc','ab','bb');
+--------------------------+
| replace('abc','ab','bb') |
+--------------------------+
| bbc                      |
+--------------------------+
1 row in set (0.01 sec)
```

### 2.7 TRIM

TRIM(str) removes spaces at the beginning and end of a string

```mysql> select trim(' a bc ');
+----------------+
| trim(' a bc ') |
+----------------+
| a bc           |
+----------------+
1 row in set (0.00 sec)
```

### 2.8 SUBSTRING

SUBSTRING(str,x,y) intercept string STR starts at x and ends at Y

```mysql> select substring('abcd',1,1);
+-----------------------+
| substring('abcd',1,1) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.00 sec)
```

### 2.9 LOCATE,POSITION,INSTR

LOCATE(s,str), POSITION(s in str), INSTR(str,s) function the same, that is, return the string s at the beginning of str, we need to pay attention to the order of entry among the three functions

```mysql> select locate('a','abc'),position('a' in 'abc'),instr('abc','a');
+-------------------+------------------------+------------------+
| locate('a','abc') | position('a' in 'abc') | instr('abc','a') |
+-------------------+------------------------+------------------+
|                 1 |                      1 |                1 |
+-------------------+------------------------+------------------+
1 row in set (0.00 sec)
```

### 2.10 REVERSE

REVERSE(str) inverts the string sequence

```mysql> select REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)
```

### 2.11 FIELD

FIELD(s,s1,s2...) Returns the string s in s1, s2...If not, return 0.

```mysql> select FIELD('a','ab','a');
+---------------------+
| FIELD('a','ab','a') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)
```

### 2.12 FIND_IN_SET

FIND_IN_SET(s1,s2) returns the position of string S1 at string s2, similar to FIELD, but note the parameters

```mysql> select FIND_IN_SET('a','caba,aa,a');
+------------------------------+
| FIND_IN_SET('a','caba,aa,a') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.00 sec)
```

Next we'll end MySQL's time function.

Tags: MySQL less Java

Posted on Wed, 24 Jun 2020 12:55:28 -0400 by xgab