0%

MySQL_字串處理

sample table-employee

1
2
3
4
5
6
7
8
9
10
11
12
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
| 1 | Robin | Jackman | Software Engineer | 5500 | 2001-10-12 | NULL |
| 2 | Taylor | Edward | Software Architect | 7200 | 2002-09-21 | NULL |
| 3 | Vivian | Dickens | Database Administrator | 6000 | 2012-08-29 | NULL |
| 4 | Harry | Clifford | Database Administrator | 6800 | 2015-12-10 | NULL |
| 5 | Eliza | Clifford | Software Engineer | 4750 | 1998-10-19 | NULL |
| 6 | Nancy | Newman | Software Engineer | 5100 | 2007-01-23 | NULL |
| 7 | Melinda | Clifford | Project Manager | 8500 | 2013-10-29 | NULL |
| 8 | Jack | Chan | Test Engineer | 6500 | 2018-09-07 | NULL |
| 9 | Harley | Gilbert | Software Architect | 8000 | 2000-07-17 | NULL |
+----+------------+-----------+------------------------+--------+---------------------

字串拼接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CONCAT
$ select CONCAT(first_name, ", ", last_name) as full_name from employee;
+-------------------+
| full_name |
+-------------------+
| Robin, Jackman |
| Taylor, Edward |
| Vivian, Dickens |
| Harry, Clifford |
| Eliza, Clifford |
| Nancy, Newman |
| Melinda, Clifford |
| Jack, Chan |
| Harley, Gilbert |
+-------------------+

CONCAT_WS
$ select CONCAT_WS("-", first_name, last_name, title) as employee_title fro
m employee;
+---------------------------------------+
| employee_title |
+---------------------------------------+
| Robin-Jackman-Software Engineer |
| Taylor-Edward-Software Architect |
| Vivian-Dickens-Database Administrator |
| Harry-Clifford-Database Administrator |
| Eliza-Clifford-Software Engineer |
| Nancy-Newman-Software Engineer |
| Melinda-Clifford-Project Manager |
| Jack-Chan-Test Engineer |
| Harley-Gilbert-Software Architect |
+---------------------------------------+

取某些字元

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
substring(column, 起始字元排序, 結束字元排序)
$ select CONCAT(first_name, " ", last_name, " was hired on ", substring(hire_date, 1, 4)) as info from employee;
+------------------------------------+
| info |
+------------------------------------+
| Robin Jackman was hired on 2001 |
| Taylor Edward was hired on 2002 |
| Vivian Dickens was hired on 2012 |
| Harry Clifford was hired on 2015 |
| Eliza Clifford was hired on 1998 |
| Nancy Newman was hired on 2007 |
| Melinda Clifford was hired on 2013 |
| Jack Chan was hired on 2018 |
| Harley Gilbert was hired on 2000 |
+------------------------------------+

替換某些字元

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
REPLACE(column, 欲替換的字元, 替換上的字元)
把software改成hardware
$ select CONCAT(first_name, " ", last_name) as full_name, REPLACE(title, "Soft", "Hard") as new_title from employee;
+------------------+------------------------+
| full_name | new_title |
+------------------+------------------------+
| Robin Jackman | Hardware Engineer |
| Taylor Edward | Hardware Architect |
| Vivian Dickens | Database Administrator |
| Harry Clifford | Database Administrator |
| Eliza Clifford | Hardware Engineer |
| Nancy Newman | Hardware Engineer |
| Melinda Clifford | Project Manager |
| Jack Chan | Test Engineer |
| Harley Gilbert | Hardware Architect |
+------------------+------------------------+

反轉字串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ select CONCAT(first_name, " ", last_name) as full_name, REVERSE(title) as
new_title from employee;

+------------------+------------------------+
| full_name | new_title |
+------------------+------------------------+
| Robin Jackman | reenignE erawtfoS |
| Taylor Edward | tcetihcrA erawtfoS |
| Vivian Dickens | rotartsinimdA esabataD |
| Harry Clifford | rotartsinimdA esabataD |
| Eliza Clifford | reenignE erawtfoS |
| Nancy Newman | reenignE erawtfoS |
| Melinda Clifford | reganaM tcejorP |
| Jack Chan | reenignE tseT |
| Harley Gilbert | tcetihcrA erawtfoS |
+------------------+------------------------+

字串長度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ select char_length(CONCAT(first_name, last_name)) as fullname_length from
employee;
+-----------------+
| fullname_length |
+-----------------+
| 12 |
| 12 |
| 13 |
| 13 |
| 13 |
| 11 |
| 15 |
| 8 |
| 13 |
+-----------------+

改字串大小寫

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ select UPPER(first_name) as first_name from employee;
+------------+
| first_name |
+------------+
| ROBIN |
| TAYLOR |
| VIVIAN |
| HARRY |
| ELIZA |
| NANCY |
| MELINDA |
| JACK |
| HARLEY |
+------------+