в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:02Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/String/String Case Function - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия