в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:04Z

1 версия

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

==Functions in SQL: Manipulating Strings==



<source lang="sql">
/*
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;

</source>

==Using String Functions in Your SQL Statements==



<source lang="sql">
/*
mysql> Drop table DVDs;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE DVDs (
-> ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(60) NOT NULL,
-> NumDisks TINYINT NOT NULL DEFAULT 1,
-> RatingID VARCHAR(4) NOT NULL,
-> StatID CHAR(3) NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.25 sec)
mysql> INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
-> VALUES ("Christmas", 1, "NR", "s1"),
-> ("Doc", 1, "G", "s2"),
-> ("Africa", 1, "PG", "s1"),
-> ("Falcon", 1, "NR", "s2"),
-> ("Amadeus", 1, "PG", "s2"),
-> ("Show", 2, "NR", "s2"),
-> ("View", 1, "NR", "s1"),
-> ("Mash", 2, "R", "s2");
Query OK, 8 rows affected (0.10 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT Name, CHAR_LENGTH(Name) AS CharLength
-> FROM DVDs
-> WHERE CHAR_LENGTH(Name)>5
-> ORDER BY Name;
+-----------+------------+
| Name | CharLength |
+-----------+------------+
| Africa | 6 |
| Amadeus | 7 |
| Christmas | 9 |
| Falcon | 6 |
+-----------+------------+
4 rows in set (0.05 sec)
*/
Drop table DVDs;
CREATE TABLE DVDs (
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(60) NOT NULL,
NumDisks TINYINT NOT NULL DEFAULT 1,
RatingID VARCHAR(4) NOT NULL,
StatID CHAR(3) NOT NULL
)
ENGINE=INNODB;
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES ("Christmas", 1, "NR", "s1"),
("Doc", 1, "G", "s2"),
("Africa", 1, "PG", "s1"),
("Falcon", 1, "NR", "s2"),
("Amadeus", 1, "PG", "s2"),
("Show", 2, "NR", "s2"),
("View", 1, "NR", "s1"),
("Mash", 2, "R", "s2");

SELECT Name, CHAR_LENGTH(Name) AS CharLength
FROM DVDs
WHERE CHAR_LENGTH(Name)>5
ORDER BY Name;

</source>

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

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

в 13:46, 26 мая 2010

Admin: 1 версия