/*
mysql> Select * from StudentExam;
+-----------+------+------------+
| StudentID | Mark | Comments |
+-----------+------+------------+
| 10 | 46 | Java |
| 10 | 65 | C# |
| 10 | 79 | JavaScript |
| 11 | 66 | Java |
| 11 | 85 | C# |
| 11 | 99 | JavaScript |
+-----------+------+------------+
6 rows in set (0.00 sec)
mysql> /* Real command */
mysql> SELECT
-> CONCAT(RIGHT(Name, LENGTH(Name) - INSTR(Name, ' ') + 1),
-> ', ', LEFT(Name, INSTR(Name, ' ') - 1))
-> AS StudentName
-> FROM Student
-> ORDER BY StudentName;
+---------------+
| StudentName |
+---------------+
| But, Cory |
| Harvests, JJ |
| Wang, Joe |
+---------------+
3 rows in set (0.04 sec)
*/
/* Prepare the data */
DROP TABLE Student;
CREATE TABLE Student (
StudentID INT NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL
)TYPE = InnoDB;
/* Insert data for testing */
INSERT INTO Student (StudentID,Name) VALUES (1,'Joe Wang');
INSERT INTO Student (StudentID,Name) VALUES (2,'Cory But');
INSERT INTO Student (StudentID,Name) VALUES (3,'JJ Harvests');
Select * from StudentExam;
/* Real command */
SELECT
CONCAT(RIGHT(Name, LENGTH(Name) - INSTR(Name, ' ') + 1),
', ', LEFT(Name, INSTR(Name, ' ') - 1))
AS StudentName
FROM Student
ORDER BY StudentName;