Basic functions of the MySQL stored procedure (iii)

(1). String class

First define a string variable: set @str = "LXL";

CHARSET (str)//return string character set

Select CharSet (@str);
+---------------+
| CharSet (@str) |
+---------------+
| UTF8 |
+---------------+

CONCAT (string [,...])//connection string

Select Concat (@str, "Hello");
+----------------------+
| Concat (@str, "Hello") |
+----------------------+
| Abcdefghello |
+----------------------+

INSTR (string, substring)//returns the position where substring first appeared in string, no return 0

Select InStr (@str, ' de ');
+------------------+
| InStr (@str, ' de ') |
+------------------+
| 4 |
+------------------+

Note: The subscript of string starts at 1 and is not 0.

Select InStr (@str, ' oo ');
+------------------+
| InStr (@str, ' oo ') |
+------------------+
| 0 |
+------------------+

LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position

Select locate (' de ', @str, 2);
+---------------------+
| Locate (' de ', @str, 2) |
+---------------------+
| 4 |
+---------------------+

LCASE (string) or lower (string),//convert to lowercase

Set @str1 = "ASDFD";

Select LCase (@str1);
+--------------+
| LCase (@str1) |
+--------------+
| ASDFD |
+--------------+

Select lower (@str1);
+--------------+
| Lower (@str1) |
+--------------+
| ASDFD |
+--------------+

Uase (String) or Uppper (string),//Convert to uppercase

Select UCase (@str1);
+--------------+
| UCase (@str1) |
+--------------+
| ASDFD |
+--------------+

Select Upper (@str1);
+--------------+
| Upper (@str1) |
+--------------+
| ASDFD |
+--------------+

Left (string, length)//The length of the character from string2

Set @str1 = "ASDFD";

Select Left (@str, 3);
+--------------+
| Left (@str, 3) |
+--------------+
| ABC |
+--------------+

Right (String,length)//fetch string last length character

Select Right (' Adfsfsdf ', 3);
+---------------------+
| Right (' Adfsfsdf ', 3) |
+---------------------+
| SDF |
+---------------------+

Length (String)//string

Select Length (@str);
+--------------+
| Length (@str) |
+--------------+
| 7 |
+--------------+

Load_file (file_name)//read content from File

Create a new Test.txt file on the desktop, "Aaaaaaaaaaaaaaaa Hello"

Select Load_file (@path);
+----------------------+
| Load_file (@path) |
+----------------------+
| AAAAAAAAAAAAAAAA Hello |
+----------------------+

Lpad (string, length, pad)//repeat pad to start with string until string length

Set @str1 = ' ASDFD ';

Select Lpad (@str1, ' xx ');
+----------------------+
| Lpad (@str1, x, ' xx ') |
+----------------------+
| XXXXXXXXXXXXXXXASDFD |
+----------------------+

Rpad (string, length, pad)//after STR with pad, until length

Select Rpad (@str, ' xx ');
+----------------------+
| Rpad (@str, x, ' xx ') |
+----------------------+
| abcdefgxxxxxxxxxxxxx |
+----------------------+

LTRIM (String)//Remove front-end spaces

Set @str1 = "ASDFD";

Select LTrim (@str1);
+--------------+
| LTrim (@str1) |
+--------------+
| ASDFD |
+--------------+

RTRIM (String)//Remove back-end spaces

Set @str1 = "Asssss";

Select RTrim (@str1);
+--------------+
| RTrim (@str1) |
+--------------+
| Asssss |
+--------------+

TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position

Default delete before and after spaces:

Select Trim (' Brrr ');
+------------------+
| Trim (' Brrr ') |
+------------------+
| Brrr |
+------------------+

Delete the specified first word such as ', '

Select Trim (Leading ', ' from ',,,, dfdfdf,,,, ');
+-----------------------------------------+
| Trim (leading ', ' from ',,,, dfdfdf,,,, ') |
+-----------------------------------------+
| DFDFDF,,,, |
+-----------------------------------------+

Delete specified such as ', '

Select Trim (trailing ', ' from ',,,, sdfdfd,,,, ');
+------------------------------------------+
| Trim (trailing ', ' from ',,,, sdfdfd,,,, ') |
+------------------------------------------+
| ,,,, SDFDFD |
+------------------------------------------+

Delete specified kinsoku characters

Select Trim (both ', ' from ',,,, sdfsdfsdf,,,, ');
+-----------------------------------------+
| Trim (both ', ' from ',,,, sdfsdfsdf,,,, ') |
+-----------------------------------------+
| SDFSDFSDF |
+-----------------------------------------+

REPEAT (String, count)//repeats the string count times

Select Repeat (@str, 3);
+-----------------------+
| Repeat (@str, 3) |
+-----------------------+
| ABCDEFGABCDEFGABCDEFG |
+-----------------------+

Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str

Select Replace (@str, ' abc ', ' AAA ');
+---------------------------+
| Replace (@str, ' abc ', ' AAA ') |
+---------------------------+
| AAADEFG |
+---------------------------+

STRCMP (string1, string2)//character comparison two string size

Set @str = "ABCDEFG";

Set @str1 = "AAAAAAAAAAA";

Select strcmp (@str, @str1);
+--------------------+
| strcmp (@str, @str1) |
+--------------------+
| 1 |
+--------------------+

Set @str1 = "BBBB";

Select strcmp (@str, @str1);
+--------------------+
| strcmp (@str, @str1) |
+--------------------+
| -1 |
+--------------------+

Returns 1 if the first string is large, or 1.

SUBSTRING (str, position [, length])//starting with the position of STR, take the length of characters

Set @str = "ABCDEFG";

Select substring (@str, 2, 3);
+---------------------+
| SUBSTRING (@str, 2, 3) |
+---------------------+
| BCD |
+---------------------+

Space (count)//Generate Count of spaces

Select Concat (Space (5), ' AAAA ');
+-------------------------+
| Concat (Space (5), ' AAAA ') |
+-------------------------+
| AAAA |
+-------------------------+

(2). Math class

ABS (NUMBER2)//Absolute value

Select ABS (-3);
+---------+
| ABS (-3) |
+---------+
| 3 |
+---------+
BIN (Decimal_number)//decimal into binary

Select Bin (8);
+--------+
| Bin (8) |
+--------+
| 1000 |
+--------+

CEILING (NUMBER2)//Up rounding

Select Ceiling (3.2);
+--------------+
| Ceiling (3.2) |
+--------------+
| 4 |
+--------------+

Floor (NUMBER2)//Down rounding

Select Floor (4.999);
+--------------+
| Floor (4.999) |
+--------------+
| 4 |
+--------------+

CONV (number2,from_base,to_base)//Binary conversion

Select conv (8,10,2);
+--------------+
| Conv (8,10,2) |
+--------------+
| 1000 |
+--------------+

Select conv (17,10,16);
+----------------+
| Conv (17,10,16) |
+----------------+
| 11 |
+----------------+

FORMAT (number,decimal_places)//reserved decimal digits, rounding

Select Format (4.12367823,3);
+----------------------+
| Format (4.12367823,3) |
+----------------------+
| 4.124 |
+----------------------+

Hex (Decimalnumber)//Turn hex

Select Hex (18);
+---------+
| Hex (18) |
+---------+
| 12 |
+---------+

Select Hex (' Hello ');
+--------------------+
| Hex (' How are You ') |
+--------------------+
| e4bda0e5a5bde59097 |
+--------------------+
: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) to return 19

LEAST (number, number2 [,..])//Find minimum

Select least (2,4,6,3,8,5);
+--------------------+
| Least (2,4,6,3,8,5) |
+--------------------+
| 2 |
+--------------------+

MOD (numerator, denominator)//redundancy

Select mod (10,3);
+-----------+
| MoD (10,3) |
+-----------+
| 1 |
+-----------+

Power (number, Power)//Index

Select power (2,3);
+------------+
| Power (2,3) |
+------------+
| 8 |
+------------+
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal place]

Note: Return types are not all integers, such as:
(1) The default becomes an integer value

Mysql> Select round (1.23);
+-------------+
| Round (1.23) |
+-------------+
| 1 |
+-------------+
1 row in Set (0.00 sec)

Mysql> Select round (1.56);
+-------------+
| Round (1.56) |
+-------------+
| 2 |
+-------------+
1 row in Set (0.00 sec)



(2) You can set the number of decimal digits, return the floating-point data

Mysql> Select round (1.567,2);
+----------------+
| Round (1.567,2) |
+----------------+
| 1.57 |
+----------------+
1 row in Set (0.00 sec)

Sign (NUMBER2)//