首先我們有一筆資料如下

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)