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('sa','sssb'),strcmp('s','');
strcmp('ss','a')	strcmp('','ssa')	strcmp('sa','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','sa'),strcmp('','s');
strcmp('a','ss')	strcmp('ssa','')	strcmp('sssb','sa')	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;