MySQL function learning NOTE 2: character Functions

MySQL function learning NOTE 2: character Functions

1. Calculate the number of characters and length of a string-CHAR_LENGTH (s)

CHAR_LENGTH (str): Returns the number of characters contained in str.

Mysql> select CHAR_LENGTH ('mysql ');

+ ---------------------- +

| CHAR_LENGTH ('mysql') |

+ ---------------------- +

| 5 |

+ ---------------------- +

2. Merge characters-CONCAT (s1, s2 ,...)AndCONCAT_WS (x, s1, s2 ,...)

CONCAT (s1, s2 ,...): The return result is the string generated by the connection parameter. There may be one or more parameters. If any return value is NULL, the return value is NULL.

Mysql> select CONCAT ('mysql', '', '5.5','', NULL, 'function ');

+ ----------------------------------------------- +

| CONCAT ('mysql', '', '5.5','', NULL, 'function') |

+ ----------------------------------------------- +

| NULL |

+ ----------------------------------------------- +

CONCAT_WS (x, s1, s2 ,...): CONCAT With Separator, which is a special form of CONCAT. The first parameter X is the delimiter of other parameters. The separator is located between the strings to be connected. The delimiter can be a string or another parameter. If the Delimiter is NULL, the result is NULL.

Mysql> select CONCAT_WS ('.', 'David ', 'tiany'), CONCAT_WS (NULL, 'mysql', '5. 5 ');

+ ------------------------------- +

| CONCAT_WS ('.', 'David ', 'tiany') | CONCAT_WS (NULL, 'mysql', '5. 5') |

+ ------------------------------- +

| David. Tian | NULL |

+ ------------------------------- +

3. Replace string functions-INSERT (s1, x, len, s2)

INSERT (s1, x, len, s2): Returns string s1, whose substring starts at position x and is replaced by string s2. If x exceeds the string length, the return value is the original string. If len is longer than other strings, it is replaced by x. If any parameter is NULL, the return value is NULL.

Mysql> select INSERT ('softtekian ', 2, 4 ,'! @ # $ ') AS c1,

-> INSERT ('softtekian ',-,' @ ') as c2,

-> INSERT ('softtekian ', 3,100, '$') as c3,

-> INSERT ('softtekian ', 2, 4,' % @ ') as c4;

+ ------------ + ------ + ---------- +

| C1 | c2 | c3 | c4 |

+ ------------ + ------ + ---------- +

| S! @ # $ Ekian | Softtekian | So $ | S % @ ekian |

+ ------------ + ------ + ---------- +

4. Letter Case Conversion Function-LOWER (s ), LCASE (s),UPPER (s),UCASE (s)

LOWER (str)AndLCASE (str): Converts all the letters in the string 'str' to lowercase letters.

Mysql> select LOWER ('mysql and Oracle ASM ') as c1, LCASE ('database Administrator') as c2;

+ ---------------------- + ------------------------ +

| C1 | c2 |

+ ---------------------- + ------------------------ +

| Mysql and oracle asm | database administrator |

+ ---------------------- + ------------------------ +

UPPER (str)AndUCASE (str): All the letters in the str string can be converted into uppercase letters.

Mysql> select UPPER ('Sunshine. M') c1, UCASE ('Sunshine. M') c2;

+ ------------- +

| C1 | c2 |

+ ------------- +

| SUNSHINE. MA | SUNSHINE. MA |

+ ------------- +

5. Get the specified length string:LEFT (s, n ), RIGHT (s, n)

LEFT (s, n): Returns n to the leftmost characters of the string s.

Mysql> select LEFT ('this is a testing email ', 7) as c1;

+ --------- +

| C1 |

+ --------- +

| This is |

+ --------- +

RIGHT (s, n): Returns the rightmost n characters of the str string.

Mysql> select RIGHT ('this is a testing email ', 7) as c1;

+ --------- +

| C1 |

+ --------- +

| G email |

+ --------- +

6. Fill in the string function:LPAD (s1, len, s2),RPAD (s1, len, s2)

LPAD (s1, len, s2): Return string s1, left of which is filled by string s2 to len length. If s1 is longer than len, the return value is shortened to len.

Mysql> select LPAD ('hello', 4, '%') as c1, LPAD ('hello', 10, '*') as c2;

+ ------ + ------------ +

| C1 | c2 |

+ ------ + ------------ +

| Hell | ***** Hello |

+ ------ + ------------ +

RPAD (s1, len, s2): Return string s1. If String s2 fills string s1 with a length greater than len, the return value is shortened to len.

Mysql> select RPAD ('hello', 4, '%') as c1, RPAD ('hello', 10, '*') as c2;

+ ------ + ------------ +

| C1 | c2 |

+ ------ + ------------ +

| Hell | Hello ***** |

+ ------ + ------------ +

7. Remove the space string function:LTRIM (s),RTRIM (s),TRIM (s)

LTRIM (s): Returns string s. The space character on the left of the string is deleted.
Mysql> select LTRIM ('book') as c1;

+ --------- +

| C1 |

+ --------- +

| Book |

+ --------- +

RTRIM (s): Returns string s. The space character on the right of the string is deleted.
Mysql> select RTRIM ('book') as c1;

+ --------- +

| C1 |

+ --------- +

| Book |

+ --------- +

TRIM (s): Returns string s. space characters on both sides of the string are deleted.

Mysql> select TRIM ('book') as c1;
+ ------ +
| C1 |
+ ------ +
| Book |
+ ------ +
 

8. Delete the function of the specified string:TRIM (s1 FROM s)

TRIM (s1 FROM s): Delete all substrings s1 at both ends of string s. S1 is optional. If not specified, spaces are deleted.

Mysql> select TRIM ('boook') as c1, TRIM ('xy' FROM 'xyxydxydxyxyxyxy') as c2;

+ ------- + ------ +

| C1 | c2 |

+ ------- + ------ +

| Boook | DxyD |

+ ------- + ------ +

9. Repeated string generation functions:REPEAT (s, n)

REPEAT (s, n): Returns a string consisting of repeated string s. The number of string s is equal to n. If n <= 0, an empty string is returned. If s or n is NULL, NULL is returned.

Mysql> select REPEAT ('abc', 3) as c1, REPEAT ('abc',-1) as c2, REPEAT ('abc', NULL) as c3;

+ ----------- + ------ +

| C1 | c2 | c3 |

+ ----------- + ------ +

| Abcabcabc | NULL |

+ ----------- + ------ +

10. Space function:SPACE (n)

SPACE (n): Returns a string consisting of n spaces.

Mysql> select CONCAT (', SPACE (6),') AS c1, CHAR_LENGTH (SPACE (6) AS C2;

+ ---------- + ---- +

| C1 | C2 |

+ ---------- + ---- +

| () | 6 |

11. Replacement functions:REPLACE (s, s1, s2)

REPLACE (s, s1, s2): Replace all strings s1 in string s with string s2.

Mysql> select REPLACE ('xxx .mysql.com ', 'x', 'w') as c1;

+ --------------- +

| C1 |

+ --------------- +

| Www.mysql.com |

12. String size comparison function:STRCMP (s1, s2)

STRCMP (s1, s2): If all strings are the same, 0 is returned. If the first parameter is smaller than the second according to the current classification order,-1 is returned. Otherwise, 1 is returned.

Mysql> select STRCMP ('txt ', 'txta') as c1, STRCMP ('txta ', 'txt') as c2, STRCMP ('txt ', 'txt ') as c3;

+ ---- +

| C1 | c2 | c3 |

+ ---- +

|-1 | 1 | 0 |

+ ---- +

13. String truncation function:SUBSTRING (s, n, len),MID (s, n, len)

SUBSTRING (s, n, len): Return a substring with the length of len from string s. The starting position is n. If n is a negative number, the position of the substring starts with n characters at the end of the string, that is, the nth character at the end of the substring. If len is omitted, it is taken to the end.

Mysql> select SUBSTRING ('breask', 5) as c1,

-> SUBSTRING ('breaskfast ',) as c2,

-> SUBSTRING ('Breakfast ',-3) as c3,

-> SUBSTRING ('Breakfast ',-5, 3) as c4;

+ -------- + ----- +

| C1 | c2 | c3 | c4 |

+ -------- + ----- +

| Skfast | skf | ast | kfa |

+ -------- + ----- +

MID (s, n, len): Same as SUBSTRING (s, n, len.

Mysql> select MID ('breaskfast ', 5) as c1, MID ('breaskfast',) as c2, MID ('Breakfast ',-3) as c3, MID ('Breakfast ',-5, 3) as c4; + -------- + ----- +

| C1 | c2 | c3 | c4 |

+ -------- + ----- +

| Skfast | skf | ast | kfa |

+ -------- + ----- +

14. Matched substring start position function:LOCATE (s1, s2),POSITION (s1 IN s2),INSTR (s2, s1)

LOCATE (s1, s2): Returns the starting position of substring s1 in string s2.

POSITION (s1 IN s2): Returns the starting position of substring s1 in string s2.

INSTR (s2, s1): Returns the starting position of substring s1 in string s2.

Mysql> select LOCATE ('ball', 'football') c1,

-> POSITION ('ball' IN 'football') c2,

-> INSTR ('football', 'ball') c3;

+ ---- +

| C1 | c2 | c3 |

+ ---- +

| 5 | 5 | 5 |

+ ---- +

15. String reverse functions:REVERSE (s)

REVERSE (s): Reverse string s. The returned string is in the opposite order of string s.

Mysql> select REVERSE ('I love you') as c1;

+ ------------ +

| C1 |

+ ------------ +

| Uoy evol I |

+ ------------ +

16. Return the string function at the specified position:ELT (n, s1, s2, s3,..., Sn)

ELT (n, s1, s2, s3,..., Sn): If n = 1, return string S1. If n = 2, return string S2, and so on. If n is less than 1 or greater than the number of parameters, the return value is NULL.

Mysql> select ELT (3, '1st', '2nd ', '3rd') as c1, ELT (3, 'oracle ', 'mysql') as c2;

+ ------ +

| C1 | c2 |

+ ------ +

| 3rd | NULL |

+ ------ +

17. Return the function at the specified string position:FIELD (s, s1, s2 ,...)

FIELD (s, s1, s2 ,...): Returns the position of the string s that appears for the first time in the list s1, s2,.... If s is not found, the return value is 0. If s is NULL, the return value is 0 because NULL cannot be compared with any value.

Mysql> select FIELD ('hi', 'hihi', 'hei', 'Hi', 'bas, 'ciao') as c1, FIELD ('hi ', 'H', 'lo', 'hilo', 'foo') as c2;

+ ---- +

| C1 | c2 |

+ ---- +

| 3 | 0 |

+ ---- +

18. The function that returns the position of the substring:FIND_IN_SET (s1, s2)

FIND_IN_SET (s1, s2): Returns the position where string s1 appears in string s2. The string list is a list of strings separated by commas. If s1 is not in s2 or s2 is a Null String, 0 is returned. If any parameter is NULL, the return value is NULL. S1 cannot contain a comma (,).

Mysql> select FIND_IN_SET ('hi', 'hihi, Hey, Hi, bas ') as c1;

+ ---- +

| C1 |

+ ---- +

| 3 |

+ ---- +

19. Function for selecting strings:MAKE_SET (x, s1, s2 ,...)

MAKE_SET (x, s1, s2 ,...): Returns a string consisting of the corresponding bits specified by the binary number of x. s1 corresponds to bit 1, s2 corresponds to bit 01, and so on. The NULL Value in s1, s2. .. will not be added to the result.

Mysql> select MAKE_SET (1, 'A', 'B', 'C') as c1, MAKE_SET (1 | 4, 'Hello', 'Nice ', 'World ') as c2, MAKE_SET (1 | 4, 'Hello', 'Nice ', NULL, 'World') as c3, MAKE_SET (0, 'A',' B ', 'C') as c4;

+ ---- + ------------- + ------- + ---- +

| C1 | c2 | c3 | c4 |

+ ---- + ------------- + ------- + ---- +

| A | hello, world | hello |

+ ---- + ------------- + ------- + ---- +

Note:

The binary value of 1 is 0001, the binary value of 4 is 0101, the binary value of 1 and 4 is 1st, from right to left 3rd bits and bits are 1.

MAKE_SET (1, 'A', 'B', 'C'): returns 1st strings;

MAKE_SET (1 | 4, 'Hello', 'Nice ', 'World'): returns a string consisting of 1st and 3rd characters starting from the left end;

MAKE_SET (1 | 4, 'Hello', 'Nice ', NULL, 'World'): the NULL value is not added to the result. Therefore, only the first string is returned;

MAKE_SET (0, 'A', 'B', 'C'): returns an empty string.

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: indicate the source for reprinting. Otherwise, you will be held legally liable for the consequences!