SQL/MySQL/String/String Case Function

String function: upper case

   <source lang="sql">

/* 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;

      </source>