set names latin1;
set @@collation_connection=latin1_german2_ci;
select @@collation_connection;
@@collation_connection
latin1_german2_ci
drop table if exists t1;
create table t1 (a char (20) not null, b int not null auto_increment, index (a,b));
insert into t1 (a) values ('ä'),('ac'),('ae'),('ad'),('Äc'),('aeb');
insert into t1 (a) values ('üc'),('uc'),('ue'),('ud'),('Ü'),('ueb'),('uf');
insert into t1 (a) values ('ö'),('oc'),('Öa'),('oe'),('od'),('Öc'),('oeb');
insert into t1 (a) values ('s'),('ss'),('ß'),('ßb'),('ssa'),('ssc'),('ßa');
insert into t1 (a) values ('eä'),('uü'),('öo'),('ää'),('ääa'),('aeae');
insert into t1 (a) values ('q'),('a'),('u'),('o'),('é'),('É'),('a');
select a,b from t1 order by a,b;
a	b
a	1
a	2
ac	1
ad	1
ä	1
ae	2
ää	1
aeae	2
ääa	1
aeb	1
Äc	1
é	1
É	2
eä	1
o	1
oc	1
od	1
ö	1
oe	2
Öa	1
oeb	1
Öc	1
öo	1
q	1
s	1
ss	1
ß	2
ssa	1
ßa	2
ßb	1
ssc	1
u	1
uc	1
ud	1
ue	1
Ü	2
ueb	1
üc	1
uf	1
uü	1
select a,b from t1 order by upper(a),b;
a	b
a	1
a	2
ac	1
ad	1
ä	1
ae	2
ää	1
aeae	2
ääa	1
aeb	1
Äc	1
é	1
É	2
eä	1
o	1
oc	1
od	1
ö	1
oe	2
Öa	1
oeb	1
Öc	1
öo	1
q	1
s	1
ss	1
ß	2
ssa	1
ßa	2
ßb	1
ssc	1
u	1
uc	1
ud	1
ue	1
Ü	2
ueb	1
üc	1
uf	1
uü	1
select a from t1 order by a desc;
a
uü
uf
üc
ueb
Ü
ue
ud
uc
u
ssc
ßb
ßa
ssa
ß
ss
s
q
öo
Öc
oeb
Öa
oe
ö
od
oc
o
eä
É
é
Äc
aeb
ääa
aeae
ää
ae
ä
ad
ac
a
a
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
select * from t1 where a like "ö%";
a	b
ö	1
Öa	1
Öc	1
öo	1
select * from t1 where a like binary "%É%";
a	b
É	2
select * from t1 where a like "%Á%";
a	b
a	1
a	2
ac	1
ad	1
ae	2
aeae	2
ääa	1
aeb	1
Öa	1
ssa	1
ßa	2
select * from t1 where a like "%U%";
a	b
u	1
uc	1
ud	1
ue	1
ueb	1
uf	1
uü	1
select * from t1 where a like "%ss%";
a	b
ss	1
ssa	1
ssc	1
drop table t1;
select strcmp('ä','ae'),strcmp('ae','ä'),strcmp('aeq','äq'),strcmp('äq','aeq');
strcmp('ä','ae')	strcmp('ae','ä')	strcmp('aeq','äq')	strcmp('äq','aeq')
0	0	0	0
select strcmp('ss','ß'),strcmp('ß','ss'),strcmp('ßs','sss'),strcmp('ßq','ssq');
strcmp('ss','ß')	strcmp('ß','ss')	strcmp('ßs','sss')	strcmp('ßq','ssq')
0	0	0	0
select strcmp('ä','af'),strcmp('a','ä'),strcmp('ää','aeq'),strcmp('ää','aeaeq');
strcmp('ä','af')	strcmp('a','ä')	strcmp('ää','aeq')	strcmp('ää','aeaeq')
-1	-1	-1	-1
select strcmp('ss','ßa'),strcmp('ß','ssa'),strcmp('sßa','sssb'),strcmp('s','ß');
strcmp('ss','ßa')	strcmp('ß','ssa')	strcmp('sßa','sssb')	strcmp('s','ß')
-1	-1	-1	-1
select strcmp('ö','oö'),strcmp('Ü','uü'),strcmp('ö','oeb');
strcmp('ö','oö')	strcmp('Ü','uü')	strcmp('ö','oeb')
-1	-1	-1
select strcmp('af','ä'),strcmp('ä','a'),strcmp('aeq','ää'),strcmp('aeaeq','ää');
strcmp('af','ä')	strcmp('ä','a')	strcmp('aeq','ää')	strcmp('aeaeq','ää')
1	1	1	1
select strcmp('ßa','ss'),strcmp('ssa','ß'),strcmp('sssb','sßa'),strcmp('ß','s');
strcmp('ßa','ss')	strcmp('ssa','ß')	strcmp('sssb','sßa')	strcmp('ß','s')
1	1	1	1
select strcmp('u','öa'),strcmp('u','ö');
strcmp('u','öa')	strcmp('u','ö')
1	1
select strcmp('sä', 'ßa'), strcmp('aä', 'äx');
strcmp('sä', 'ßa')	strcmp('aä', 'äx')
-1	-1
create table t1 (a varchar(10), key(a), fulltext (a));
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
select * from t1 where a like "abc%";
a
abc
abcd
select * from t1 where a like "test%";
a
test
select * from t1 where a like "te_t";
a
test
select * from t1 where match a against ("te*" in boolean mode)+0;
a
test
drop table t1;
create table t1 (word varchar(255) not null, word2 varchar(255) not null default '', index(word));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `word` varchar(255) COLLATE latin1_german2_ci NOT NULL,
  `word2` varchar(255) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
update t1 set word2=word;
select word, word=binary 0xdf as t from t1 having t > 0;
word	t
ß	1
select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
word	t
ss	1
ß	1
select * from t1 where word=binary 0xDF;
word	word2
ß	ß
select * from t1 where word=CAST(0xDF as CHAR);
word	word2
ss	ss
ß	ß
select * from t1 where word2=binary 0xDF;
word	word2
ß	ß
select * from t1 where word2=CAST(0xDF as CHAR);
word	word2
ss	ss
ß	ß
select * from t1 where word='ae';
word	word2
ä	ä
ae	ae
select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
word	word2
ä	ä
ae	ae
select * from t1 where word between binary 0xDF and binary 0xDF;
word	word2
ß	ß
select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
word	word2
ss	ss
ß	ß
select * from t1 where word like 'ae';
word	word2
ae	ae
select * from t1 where word like 'AE';
word	word2
ae	ae
select * from t1 where word like binary 0xDF;
word	word2
ß	ß
select * from t1 where word like CAST(0xDF as CHAR);
word	word2
ß	ß
drop table t1;
CREATE TABLE t1 (
autor varchar(80) NOT NULL default '',
PRIMARY KEY  (autor)
);
INSERT INTO t1 VALUES ('Powell, B.'),('Powell, Bud.'),('Powell, L. H.'),('Power, H.'),
('Poynter, M. A. L. Lane'),('Poynting, J. H. und J. J. Thomson.'),('Pozzi, S(amuel-Jean).'),
('Pozzi, Samuel-Jean.'),('Pozzo, A.'),('Pozzoli, Serge.');
SELECT * FROM t1 WHERE autor LIKE 'Poz%' ORDER BY autor;
autor
Pozzi, S(amuel-Jean).
Pozzi, Samuel-Jean.
Pozzo, A.
Pozzoli, Serge.
DROP TABLE t1;
CREATE TABLE t1 (
s1 CHAR(5) CHARACTER SET latin1 COLLATE latin1_german2_ci
);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `s1` char(5) COLLATE latin1_german2_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
INSERT INTO t1 VALUES ('Ü');
INSERT INTO t1 VALUES ('ue');
SELECT DISTINCT s1 FROM t1;
s1
Ü
SELECT s1,COUNT(*) FROM t1 GROUP BY s1;
s1	COUNT(*)
Ü	2
SELECT COUNT(DISTINCT s1) FROM t1;
COUNT(DISTINCT s1)
1
SELECT FIELD('ue',s1), FIELD('Ü',s1), s1='ue', s1='Ü' FROM t1;
FIELD('ue',s1)	FIELD('Ü',s1)	s1='ue'	s1='Ü'
1	1	1	1
1	1	1	1
DROP TABLE t1;
create table t1 select repeat('a',4000) a;
delete from t1;
insert into t1 values ('a'), ('a '), ('a\t');
select collation(a),hex(a) from t1 order by a;
collation(a)	hex(a)
latin1_german2_ci	6109
latin1_german2_ci	61
latin1_german2_ci	6120
drop table t1;
drop table if exists t1;
create table t1 as select repeat(' ', 64) as s1;
select collation(s1) from t1;
collation(s1)
latin1_german2_ci
delete from t1;
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
select s1, hex(s1) from t1 order by s1, binary s1;
s1	hex(s1)
a	61
ae	6165
ä	E4
o	6F
oe	6F65
ö	F6
s	73
ss	7373
ß	DF
u	75
ue	7565
ü	FC
select group_concat(s1 order by binary s1) from t1 group by s1;
group_concat(s1 order by binary s1)
a
ae,ä
o
oe,ö
s
ss,ß
u
ue,ü
drop table t1;
SET NAMES latin1;
CREATE TABLE t1 (
col1 varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 collate latin1_german2_ci;
INSERT INTO t1 VALUES ('ß'),('ss'),('ss');
ALTER TABLE t1 ADD KEY ifword(col1);
SELECT * FROM t1 WHERE col1='ß' ORDER BY col1, BINARY col1;
col1
ss
ss
ß
DROP TABLE t1;
create table t1 (s1 char(5) character set latin1 collate latin1_german2_ci);
insert into t1 values (0xf6) /* this is o-umlaut */;
select * from t1 where length(s1)=1 and s1='oe';
s1
ö
drop table t1;