Use the CONCAT_WS() function to concatenate the employee names. : CONCAT_WS « String « SQL / MySQL

1. SQL / MySQL
2. String
3. CONCAT_WS

Use the CONCAT_WS() function to concatenate the employee names.

    
mysql>
mysql> CREATE TABLE Employees
    -> (
    ->     EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     EmpFN VARCHAR(20) NOT NULL,
    ->     EmpMN VARCHAR(20) NULL,
    ->     EmpLN VARCHAR(20) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # insert 7 records into the "employees" table
mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Arthur", "Smith","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Peter", "Jones","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Ann", "Smith","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Sandra", "Williams","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Andrew", "Smith","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Paul", "Jones","A");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employees (EmpFN, EmpLN, EmpMN) VALUES ("Sally", "Williams","A");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT EmpID, CONCAT_WS(' ', EmpFN, EmpMN, EmpLN) AS Name
    -> FROM Employees
    -> ORDER BY EmpLN;
+-------+-------------------+
| EmpID | Name              |
+-------+-------------------+
|     2 | Peter A Jones     |
|     6 | Paul A Jones      |
|     1 | Arthur A Smith    |
|     3 | Ann A Smith       |
|     5 | Andrew A Smith    |
|     4 | Sandra A Williams |
|     7 | Sally A Williams  |
+-------+-------------------+
7 rows in set (0.00 sec)

mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
  

Related examples in the same category