Manipulate retrieved data from the Employees table.

      
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.01 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, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
    -> CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
    -> FROM Employees
    -> ORDER BY EmpID;
+-------+-------------------+--------+
| EmpID | Name              | RegID  |
+-------+-------------------+--------+
|     1 | ARTHUR A SMITH    | arsmi1 |
|     2 | PETER A JONES     | pejon2 |
|     3 | ANN A SMITH       | ansmi3 |
|     4 | SANDRA A WILLIAMS | sawil4 |
|     5 | ANDREW A SMITH    | ansmi5 |
|     6 | PAUL A JONES      | pajon6 |
|     7 | SALLY A WILLIAMS  | sawil7 |
+-------+-------------------+--------+
7 rows in set (0.00 sec)

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

Related examples in the same category

1.	CONCAT(, [{, }...])
2.	Use the CONCAT() and LEFT() functions to create registration codes for the employees.
3.	Combining a Date and a Time into a Date-and-Time Value
4.	Canonizing Not-Quite-ISO Date Strings
5.	Add the setting NO_ZERO_IN_DATE to the SQL_MODE system variable.
6.	To combine strings rather than pull them apart, use the CONCAT( ) function.
7.	Concatenating Character Strings
8.	Concatenation can be useful for modifying column values "in place."
9.	Non-ISO results from the CONCAT( ) operation can be converted into ISO format three different ways as follows:
10.	Generate Unique Sequential Numbers
11.	Use Concat to create Initial for the name

Manipulate retrieved data from the Employees table. : CONCAT « String « SQL / MySQL

Related examples in the same category