SELECT firstname FROM employee ORDER BY firstname COLLATE latin1_german1_ci : Collate « I18N « SQL / MySQL






  1. SQL / MySQL
  2. I18N
  3. Collate

SELECT firstname FROM employee ORDER BY firstname COLLATE latin1_german1_ci

   
mysql>
mysql> CREATE TABLE employee (
    ->     id int unsigned not null auto_increment primary key,
    ->     firstname varchar(20),
    ->     lastname varchar(20),
    ->     title varchar(30),
    ->     age int,
    ->     yearofservice int,
    ->     salary int,
    ->     perks int,
    ->     email varchar(60)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen
", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pilla
i", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandi
t", "Web Designer", 24, 3, 90000, 15000, "a@gmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anch
or", "Web Designer", 27, 2, 85000, 15000, "m@mail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King
", "Programmer", 32, 3, 75000, 15000, "f@net.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac"
, "Programmer", 32, 4, 80000, 16000, "j@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sa
m", "Programmer", 28, 2, 75000, 14000, "e@yahoo.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nand
a", "Programmer", 32, 3, 70000, 10000, "a@yahoo.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra"
, "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simo
n", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Pa
rhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunte
r", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lew
is", "System Administrator", 32, 3, 100000, 13000, "roger@mail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gib
son", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harp
er", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sund
ay", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim"
, "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvin
e", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali
", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Cha
mpion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.com");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT firstname FROM employee ORDER BY firstname COLLATE latin1_german1_ci;
+-----------+
| firstname |
+-----------+
| Alok      |
| Ane       |
| Arthur    |
| Danny     |
| Edward    |
| Fred      |
| Henry     |
| Jack      |
| Jan       |
| Joe       |
| John      |
| John      |
| Kim       |
| Mary      |
| Mary      |
| Mike      |
| Paul      |
| Peter     |
| Roger     |
| Susan     |
+-----------+
20 rows in set (0.00 sec)

mysql>
mysql> drop TABLE employee;
Query OK, 0 rows affected (0.00 sec)

mysql>








Related examples in the same category

1.Set COLLATE=latin1_german1_ci
2.ORDER BY and COLLATE latin1_german2_ci
3.ALTER TABLE employee MODIFY employee VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_german2_ci;
4.ORDER BY CONVERT(firstname USING utf8) COLLATE utf8_polish_ci;
5.SELECT firstname FROM employee ORDER BY firstname COLLATE latin1_general_ci
6.SELECT firstname FROM employee ORDER BY firstname COLLATE latin1_swedish_ci
7.SELECT firstname FROM employee ORDER BY firstname COLLATE utf8_general_ci
8.COLLATE sort operator allows you ro specify a collation in your expression.
9.Create database with character set and collate
10.Change the character set name and collate name for database
11.Change collate for database
12.COLLATE utf8_romanian_ci for column definition
13.Set the character set and collate for a table
14.COLLATE 'latin1_danish_ci'
15.ORDER BY a column COLLATE latin1_swedish_ci
16.Cast COLLATE
17.Order by collate
18.Alter table to change the collate setting
19.Check the collation name
20.Get collation
21.ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_danish_ci,IMPLICIT) for operation '='