String function: concat : String Append « String « SQL / MySQL

1. SQL / MySQL
2. String
3. String Append

String function: concat

/*
mysql> Drop table Employees;
ERROR 1051 (42S02): Unknown table 'employees'
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.13 sec)

mysql> INSERT INTO Employees (EmpFN, EmpMN, EmpLN)
    -> VALUES ('John', 'P.', 'Smith'),
    ->        ('Robert', NULL, 'Schroader'),
    ->        ('Mary', 'Marie', 'Michaels'),
    ->        ('John', NULL, 'Laguci'),
    ->        ('Rita', 'C.', 'Carter'),
    ->        ('George', NULL, 'Brooks');
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

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 | JOHN P. SMITH       | josmi1 |
|     2 | ROBERT SCHROADER    | rosch2 |
|     3 | MARY MARIE MICHAELS | mamic3 |
|     4 | JOHN LAGUCI         | jolag4 |
|     5 | RITA C. CARTER      | ricar5 |
|     6 | GEORGE BROOKS       | gebro6 |
+-------+---------------------+--------+
6 rows in set (0.02 sec)

mysql>

*/       
Drop table Employees;

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;

INSERT INTO Employees (EmpFN, EmpMN, EmpLN)
VALUES ('John', 'P.', 'Smith'),
       ('Robert', NULL, 'Schroader'),
       ('Mary', 'Marie', 'Michaels'),
       ('John', NULL, 'Laguci'),
       ('Rita', 'C.', 'Carter'),
       ('George', NULL, 'Brooks');


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;
           
       

Related examples in the same category