MySQL Function - String Function

(1) A function that calculates the number and length of strings.
(2) Merge string length concat(s1,s2,... concat_ws(x,s1,s2,... )
(3) function insert(s1,x,len,s2) that replaces strings.
(4) A case-to-case conversion function.
(5) Get the functions left(s,n) and right(s,n) of the specified length string.
(6) Filling string functions LPAD(s1,len,s2) and RPAD(s1,len,s2).
(7) Functions LTRIM(s), RTRIM(s) and TRIM(s) that delete spaces.
(8) Delete the function TRIM(s1 from s) of the specified string.
(9) Repeat the function repeat(s,n) that generates strings.
(10) Spatial function SPACE(n) and replacement function REPLA(s,s1,s2).
(11) STRCMP(s1,s2), a function that compares string sizes.
(12) Get the functions SUBSTRING(s,n,len) and MID(s,n,len) of the substring.
(13) A function that matches the starting position of a substring.
(14) The function REVERSE(s) of string inversion.
(15) A function that returns a string at a specified location.
(16) Returns the function FIELD(s,s1,s2) that specifies the position of the string.
(17) The function FIND_IN_SET(S1,S2) that returns the position of the substring.
(18) Select the string function MAKE_SET(x,s1,s2).

(1) A function that calculates the number and length of strings.

1.char_length(str)
[Example] char_length function is used to calculate the number of strings. The SQL statement is as follows:

mysql> select char_length('date'),
    -> char_length('egg');
+---------------------+--------------------+
| char_length('date') | char_length('egg') |
+---------------------+--------------------+
|                   4 |                  3 |
+---------------------+--------------------+
1 row in set (0.06 sec)

2.length(str)
[Example] The length(str) function is used to calculate the length of the string. The SQL statement is as follows:

mysql> select length('date'),length('egg');
+----------------+---------------+
| length('date') | length('egg') |
+----------------+---------------+
|              4 |             3 |
+----------------+---------------+
1 row in set (0.00 sec)
(2) Merge string length concat(s1,s2,...), concat_ws(x,s1,s2,...).

1.concat(s1,s2...)
[Example] The concat function is used to connect strings, and the SQL statements are as follows:

mysql> select concat('My SQL','5.7'),
    -> concat('My',null,'SQL');
+------------------------+-------------------------+
| concat('My SQL','5.7') | concat('My',null,'SQL') |
+------------------------+-------------------------+
| My SQL5.7              | NULL                    |
+------------------------+-------------------------+
1 row in set (0.05 sec)

2.concat_ws(x,s1,s2,...)
[Example] The concat_ws function is used to connect a delimited string. The SQL statement is as follows:

mysql> select concat_ws('-','1st','2nd','3rd'),concat_ws('*','1st',null,'3rd');
+----------------------------------+---------------------------------+
| concat_ws('-','1st','2nd','3rd') | concat_ws('*','1st',null,'3rd') |
+----------------------------------+---------------------------------+
| 1st-2nd-3rd                      | 1st*3rd                         |
+----------------------------------+---------------------------------+
1 row in set (0.05 sec)
(3) function insert(s1,x,len,s2) that replaces strings.

[Example] String substitution operations are performed using insert functions. The SQL statements are as follows:

mysql> select insert('Quest',2,4,'What') as coll;
+-------+
| coll  |
+-------+
| QWhat |
+-------+
1 row in set (0.05 sec)
(4) A case-to-case conversion function.

1.LOWER(str) and LCASE(str)
[Example] The lower function or lcase function is used to convert all alphabetic characters in the string str into lowercase. The SQL statement is as follows:

mysql> select lower('BEAUTIFUL'),lcase('WeLL');
+--------------------+---------------+
| lower('beautiful') | lcase('well') |
+--------------------+---------------+
| beautiful          | well          |
+--------------------+---------------+
1 row in set (0.05 sec)

2.UPPER(str) and UCASEE(str)
[Example] Use upper or ucase functions to convert all alphabetic characters in a string to uppercase. The SQL statement is as follows:

mysql> select upper('black'),ucase('BLacK');
+----------------+----------------+
| upper('black') | ucase('BLacK') |
+----------------+----------------+
| BLACK          | BLACK          |
+----------------+----------
(5) Get the functions left(s,n) and right(s,n) of the specified length string.

1.left(s,n)
[Example] The left character in the string is returned using the left function. The SQL statement is as follows:

mysql> select left('football',5);
+--------------------+
| left('football',5) |
+--------------------+
| footb              |
+--------------------+
1 row in set (0.00 sec)

2.right(s,n)

[Example] The right function is used to return n characters to the rightmost side of the string str. The SQL statement is as follows:

mysql> select right ('football',4);
+----------------------+
| right ('football',4) |
+----------------------+
| ball                 |
+----------------------+
1 row in set (0.00 sec)
(6) Filling string functions LPAD(s1,len,s2) and RPAD(s1,len,s2).

1.LPAD(s1,;en,s2)
[Example] The LPAD function is used to fill the string. The SQL statement is as follows:

mysql> select lpad('hello',4,'??'),lpad('hello',10,'??');
+----------------------+-----------------------+
| lpad('hello',4,'??') | lpad('hello',10,'??') |
+----------------------+-----------------------+
| hell                 | ?????hello            |
+----------------------+-----------------------+
1 row in set (0.00 sec)

2.RPAD(s1,len,s2)

mysql> select rpad('hello',4,'?'),rpad('hello',10,'?');
+---------------------+----------------------+
| rpad('hello',4,'?') | rpad('hello',10,'?') |
+---------------------+----------------------+
| hell                | hello?????           |
+---------------------+----------------------+
1 row in set (0.00 sec)
(7) Functions LTRIM(s), RTRIM(s) and TRIM(s) that delete spaces.

1.LTRIM(s)
[Example] Use the LTRIM function to delete the space on the left of the string. The SQL statement is as follows:

mysql> select '( book )',
    -> concat('(',ltrim(' book '),')');
+----------+---------------------------------+
| ( book ) | concat('(',ltrim(' book '),')') |
+----------+---------------------------------+
| ( book ) | (book )                         |
+----------+---------------------------------+
1 row in set (0.05 sec)

2.RTRIM(s)
[Example] Use the RTRIM function to delete the space on the right side of the string. The SQL statement is as follows:

mysql> select '( book )',concat('(',rtrim(' book '),')');
+----------+---------------------------------+
| ( book ) | concat('(',rtrim(' book '),')') |
+----------+---------------------------------+
| ( book ) | ( book)                         |
+----------+---------------------------------+
1 row in set (0.05 sec)

3.TRIM(s)
[Example] Use TRIM function to delete the spaces on both sides of the string. The SQL statement is as follows:

mysql> select '( book )',concat('(',trim(' book '),')');
+----------+--------------------------------+
| ( book ) | concat('(',trim(' book '),')') |
+----------+--------------------------------+
| ( book ) | (book)                         |
+----------+--------------------------------+
1 row in set (0.05 sec)
(8) Delete the function TRIM(s1 from s) of the specified string.

[Example] Use TRIM(s1 from s) function to delete the characters specified at both ends of the string. The SQL statement is as follows:

mysql> select trim('xy' from 'xyxboxyokxxyxy');
+----------------------------------+
| trim('xy' from 'xyxboxyokxxyxy') |
+----------------------------------+
| xboxyokx                         |
+----------------------------------+
1 row in set (0.00 sec)
(9) Repeat the function repeat(s,n) that generates strings.

[Example] Repeat the same string using the repeat function. The SQL statement is as follows:

mysql> select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+
1 row in set (0.00 sec)
(10) Spatial function SPACE(n) and replacement function REPLA(s,s1,s2).

1.space(n)
[Example] The space function is used to generate a string consisting of spaces. The SQL statement is as follows:

mysql> select concat('(',space(6),')');
+--------------------------+
| concat('(',space(6),')') |
+--------------------------+
| (      )                 |
+--------------------------+
1 row in set (0.00 sec)

2.replace(s,s1,s2)

[Example] String substitution is performed using replace function. The SQL statement is as follows:

mysql> select replace('xxx.mysql.con','x','w');
+----------------------------------+
| replace('xxx.mysql.con','x','w') |
+----------------------------------+
| www.mysql.con                    |
+----------------------------------+
1 row in set (0.00 sec)
(11) STRCMP(s1,s2), a function that compares string sizes.
  • If s1 and s2 are the same, return 0.
  • If S1 < s2, return-1;
  • If S1 > s2, return 1

[Example] Using strcmp function to compare string size, the SQL statement is as follows:

mysql> select strcmp('txt','txt2'),
    -> strcmp('txt2','txt'),
    -> strcmp('txt','txt');
+----------------------+----------------------+---------------------+
| strcmp('txt','txt2') | strcmp('txt2','txt') | strcmp('txt','txt') |
+----------------------+----------------------+---------------------+
|                   -1 |                    1 |                   0 |
+----------------------+----------------------+---------------------+
1 row in set (0.00 sec)
(12) Get the functions SUBSTRING(s,n,len) and MID(s,n,len) of the substring.

1.substring(s,n,len)
[Example] Use the substring function to get the substring at the specified location. The SQL statement is as follows:

mysql> select substring('breakfast',5) as col1,
    -> substring('breakfast',5,3) as col2,
    -> substring('breakfast',-3) as col3,
    -> substring('breakfast',-5,3) as col4;
+-------+------+------+------+
| col1  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | ast  | kfa  |
+-------+------+------+------+
1 row in set (0.00 sec)

2.mid(s,n,len)
[Example] Use the mid() function to get the substring at the specified location. The SQL statement is as follows:

mysql> select mid('breakfast',5) as coll,
    -> mid('breakfast',5,3) as col2,
    -> mid('lunch',-3) as col3,
    -> mid('lunch',-5,3) as col4;
+-------+------+------+------+
| coll  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)
(13) A function that matches the starting position of a substring.

[Example] Use locate, position, instr functions to find the starting position of the specified substring in the string. The SQL statement is as follows:

mysql> select
    -> locate('ball','football'),
    -> position('ball' in 'football'),
    -> instr('football','ball');
+---------------------------+--------------------------------+--------------------------+
| locate('ball','football') | position('ball' in 'football') | instr('football','ball') |
+---------------------------+--------------------------------+--------------------------+
|                         5 |                              5 |                        5 |
+---------------------------+--------------------------------+--------------------------+
1 row in set (0.00 sec)
(14) The function REVERSE(s) of string inversion.

[Example] Using reverse function to invert string, the SQL statement is as follows:

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)
(15) A function that returns a string at a specified location.

[Example] The elt function is used to return the specified position string. The SQL statement is as follows:

mysql> select elt(3,'1st','2nd','3rd'),elt(3,'net','os');
+--------------------------+-------------------+
| elt(3,'1st','2nd','3rd') | elt(3,'net','os') |
+--------------------------+-------------------+
| 3rd                      | NULL              |
+--------------------------+-------------------+
1 row in set (0.00 sec)
(16) Returns the function FIELD(s,s1,s2) that specifies the position of the string.
  • Returns the position where the first character first appears in the list.

[Example] Use the field function to return the location where the specified string first appears. The SQL statement is as follows:

mysql> select
    -> field('Hi','hihi','Hey','Hi','bas') as col1,
    -> field('Hi','Hey','Lo','Hilo','foo') as col2;
+------+------+
| col1 | col2 |
+------+------+
|    3 |    0 |
+------+------+
1 row in set (0.00 sec)
(17) The function FIND_IN_SET(S1,S2) that returns the position of the substring.
  • The find_in_set() function is different from the field() function in format, but it works similarly and can return the position of the specified string in the string list.

[Example] Use the find_in_set() function to return the position of the substring in the string list. The SQL statement is as follows:

mysql> select find_in_set('Hi','hihi,Hey,Hi,bas');
+-------------------------------------+
| find_in_set('Hi','hihi,Hey,Hi,bas') |
+-------------------------------------+
|                                   3 |
+-------------------------------------+
1 row in set (0.00 sec)
(18) Select the string function MAKE_SET(x,s1,s2).

[Example] Use make_set to select the specified string according to the binary bit. The SQL statement is as follows:

mysql> select make_set(1,'a','b','c') as col1,
    -> make_set(1 |4,'hello','nice','word') as col2,
    -> make_set(1 |4,'hello','nice',null,'world') as col3,
    -> make_set(0,'a','b','c') as col4;
+------+------------+-------+------+
| col1 | col2       | col3  | col4 |
+------+------------+-------+------+
| a    | hello,word | hello |      |
+------+------------+-------+------+
1 row in set (0.00 sec)
  • The binary of 1 is 0001, 4 is 0100, the binary of 1 and 4 is 0101 after operation, and the first and third bits from right to left are 1.
  • make_set(1,'a','b','c') returns the first string; make_set(1 | 4,'hello','nice','world') returns the string consisting of the first and third strings starting from the left.
  • Null will not be added to the result, so make_set(1 | 4,'hello','nice','null','world') returns only the first string'hello'.
  • make_set(0,'a','b','c') returns an empty string.

Tags: Database MySQL SQL

Posted on Thu, 29 Aug 2019 00:23:06 -0700 by Jen_u41