String function: upper case : String Case Function « String « SQL / MySQL

1. SQL / MySQL
2. String
3. String Case Function

String function: upper case

/*
mysql> Drop table Employees;
Query OK, 0 rows affected (0.12 sec)

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.11 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.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
    ->    CONCAT(LOWER(SUBSTRING(EmpFN, 2, 2)),
    ->    LOWER(SUBSTRING(EmpLN, 2, 3)), EmpID) AS RegID
    -> FROM Employees
    -> ORDER BY EmpID;
+-------+---------------------+--------+
| EmpID | Name                | RegID  |
+-------+---------------------+--------+
|     1 | JOHN P. SMITH       | ohmit1 |
|     2 | ROBERT SCHROADER    | obchr2 |
|     3 | MARY MARIE MICHAELS | arich3 |
|     4 | JOHN LAGUCI         | ohagu4 |
|     5 | RITA C. CARTER      | itart5 |
|     6 | GEORGE BROOKS       | eoroo6 |
+-------+---------------------+--------+
6 rows in set (0.04 sec)

*/  
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(SUBSTRING(EmpFN, 2, 2)),
   LOWER(SUBSTRING(EmpLN, 2, 3)), EmpID) AS RegID
FROM Employees
ORDER BY EmpID;


           
       

Related examples in the same category