Functions in SQL: Manipulating Strings : String Function « String « SQL / MySQL

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

Functions in SQL: Manipulating Strings

/*

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;

           
       

Related examples in the same category