MySQL 字符串相关操作

首先我们有一笔资料如下

CREATE TABLE IF NOT EXISTS employee(  id INT NOT NULL AUTO_INCREMENT,  first_name VARCHAR(100) NOT NULL,  last_name VARCHAR(100) NOT NULL,  title VARCHAR(100) DEFAULT NULL,  salary DOUBLE DEFAULT NULL,  hire_date DATE NOT NULL,  notes TEXT,  PRIMARY KEY (id));INSERT INTO employee (first_name, last_name, title, salary, hire_date) VALUES     ('Robin', 'Jackman', 'Software Engineer', 5500, '2001-10-12'),    ('Taylor', 'Edward', 'Software Architect', 7200, '2002-09-21'),    ('Vivian', 'Dickens', 'Database Administrator', 6000, '2012-08-29'),    ('Harry', 'Clifford', 'Database Administrator', 6800, '2015-12-10'),    ('Eliza', 'Clifford', 'Software Engineer', 4750, '1998-10-19'),    ('Nancy', 'Newman', 'Software Engineer', 5100, '2007-01-23'),    ('Melinda', 'Clifford', 'Project Manager', 8500, '2013-10-29'),    ('Jack', 'Chan', 'Test Engineer', 6500, '2018-09-07'),    ('Harley', 'Gilbert', 'Software Architect', 8000, '2000-07-17');

SELECT CONCAT(column1,column2,...) FROM table_name; :concat可以用来合併column之字串。

可以在字符串中间穿插字串，以更美观，且可利用as更换原有之名称如CONCAT(first_name , last_name) => FullName 方便辨识。

mysql> SELECT CONCAT(first_name , ", " , last_name) as FullName FROM employee;+-------------------+| FullName          |+-------------------+| Robin, Jackman    || Taylor, Edward    || Vivian, Dickens   || Harry, Clifford   || Eliza, Clifford   || Nancy, Newman     || Melinda, Clifford || Jack, Chan        || Harley, Gilbert   |+-------------------+

SELECT CONCAT_WS("-",column1,column2,...) FROM table_name; :可以来将字符串合併并且在每个column之间穿插设置之字串。

mysql> SELECT CONCAT_WS("-",first_name,last_name,title) as FullName  FROM employee;+---------------------------------------+| FullName                              |+---------------------------------------+| 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     |+---------------------------------------+9 rows in set (0.00 sec)

SELECT SUBSTRING("Hello jojo",2,6); :可以截取片段的字串，第一个参数为起始点，预设从第一个位置开始，第二个参数为数量。

mysql> SELECT SUBSTRING("Hello jojo",2,6);+-----------------------------+| SUBSTRING("Hello jojo",2,6) |+-----------------------------+| ello j                      |+-----------------------------+1 row in set (0.00 sec)

起始参数为负的话，从后面数起。

mysql> SELECT SUBSTRING("Hello jojo",-2);+------------------------------+| SUBSTRING("Hello jojo",-2) |+------------------------------+| jo                           |+------------------------------+1 row in set (0.01 sec)

此外我们SUBSTRING可以简写成SUBSTR。

mysql> SELECT SUBSTR(title,-7) as test FROM employee;+---------+| test    |+---------+| ngineer || chitect || strator || strator || ngineer || ngineer || Manager || ngineer || chitect |+---------+

综合上面两个指令可以应用为如下

mysql> SELECT CONCAT(first_name," ",last_name,"was hired on ",SUBSTR(hire_date,1,4))    -> as information FROM employee;+-----------------------------------+| information                       |+-----------------------------------+| Robin Jackmanwas hired on 2001    || Taylor Edwardwas hired on 2002    || Vivian Dickenswas hired on 2012   || Harry Cliffordwas hired on 2015   || Eliza Cliffordwas hired on 1998   || Nancy Newmanwas hired on 2007     || Melinda Cliffordwas hired on 2013 || Jack Chanwas hired on 2018        || Harley Gilbertwas hired on 2000   |+-----------------------------------+

SELECT REPLACE("...","...","...") as person; :可以对指定字串做取代。

最后一个为我们要替换成的字串。

SELECT REPLACE("HELLO JOJO","JOJO","DIO") as person;+-----------+| person    |+-----------+| HELLO DIO |+-----------+1 row in set (0.00 sec

SELECT REVERSE("...") as person; :可以将字串颠倒。

SELECT REVERSE("OJOJ OLLEH") as person;+------------+| person     |+------------+| HELLO JOJO |+------------+1 row in set (0.01 sec)

SELECT CHAR_LENGTH("...") as total; :可以回传字串长度。

SELECT CHAR_LENGTH("HELLO JOJO") as total;+-------+| total |+-------+|    10 |+-------+1 row in set (0.01 sec)

应用在我们的资料上。

SELECT first_name,last_name, REPLACE(title,"Software","HARDWARE") as new,REVERSE(salary) as new FROM employee;+------------+-----------+------------------------+------+| first_name | last_name | new                    | new  |+------------+-----------+------------------------+------+| Robin      | Jackman   | HARDWARE Engineer      | 0055 || Taylor     | Edward    | HARDWARE Architect     | 0027 || Vivian     | Dickens   | Database Administrator | 0006 || Harry      | Clifford  | Database Administrator | 0086 || Eliza      | Clifford  | HARDWARE Engineer      | 0574 || Nancy      | Newman    | HARDWARE Engineer      | 0015 || Melinda    | Clifford  | Project Manager        | 0058 || Jack       | Chan      | Test Engineer          | 0056 || Harley     | Gilbert   | HARDWARE Architect     | 0008 |+------------+-----------+------------------------+------+

SELECT LOWER("...") , SELECT UPPER("..."); 前者可将字符串变成小写，后者大写，不影响符号及数字。

mysql> SELECT UPPER("Heelo");+----------------+| UPPER("Heelo") |+----------------+| HEELO          |+----------------+1 row in set (0.01 sec)mysql>mysql> SELECT LOWER("faDD");+---------------+| LOWER("faDD") |+---------------+| fadd          |+---------------+1 row in set (0.00 sec)