func_str.result 26.8 KB
Newer Older
1
drop table if exists t1;
2
set names latin1;
3
select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo';
4 5
hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
hello	'hello'	""hello""	'h'e'l'l'o'	hel"lo	hel'lo
6
select 'hello' 'monty';
7 8
hello
hellomonty
9
select length('\n\t\r\b\0\_\%\\');
10 11
length('\n\t\r\b\0\_\%\\')
10
12 13 14
select bit_length('\n\t\r\b\0\_\%\\');
bit_length('\n\t\r\b\0\_\%\\')
80
15
select concat('monty',' was here ','again'),length('hello'),char(ascii('h'));
16 17
concat('monty',' was here ','again')	length('hello')	char(ascii('h'))
monty was here again	5	h
18
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
19 20
locate('he','hello')	locate('he','hello',2)	locate('lo','hello',2)
1	0	4
21 22 23 24 25
select instr('hello','HE'), instr('hello',binary 'HE'), instr(binary 'hello','HE');
instr('hello','HE')	instr('hello',binary 'HE')	instr(binary 'hello','HE')
1	0	0
select position(binary 'll' in 'hello'),position('a' in binary 'hello');
position(binary 'll' in 'hello')	position('a' in binary 'hello')
26
3	0
27
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
28 29
left('hello',2)	right('hello',2)	substring('hello',2,2)	mid('hello',1,5)
he	lo	el	hello
30
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
31 32
concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1))
happy
33
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
34 35
substring_index('www.tcx.se','.',-2)	substring_index('www.tcx.se','.',1)
tcx.se	www
36
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
37 38
substring_index('www.tcx.se','tcx',1)	substring_index('www.tcx.se','tcx',-1)
www.	.se
39
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
40 41
substring_index('.tcx.se','.',-2)	substring_index('.tcx.se','.tcx',-1)
tcx.se	.se
42
select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
43 44
concat(':',ltrim('  left  '),':',rtrim('  right  '),':')
:left  :  right:
45
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
46 47
concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':')
:left: right:
48
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
49 50
concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':')
:m:y:s:
51
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
52 53
concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':')
:my:sql:
54
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
55 56
concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':')
:my:sql:
57
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
58 59
TRIM("foo" FROM "foo")	TRIM("foo" FROM "foook")	TRIM("foo" FROM "okfoo")
	ok	ok
60
select concat_ws(', ','monty','was here','again');
61 62
concat_ws(', ','monty','was here','again')
monty, was here, again
63
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
64 65
concat_ws(NULL,'a')	concat_ws(',',NULL,'')
NULL	
66
select concat_ws(',','',NULL,'a');
67
concat_ws(',','',NULL,'a')
68
,a
69
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
70 71
CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"')
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
72
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
73 74
insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
this	is a	test
75
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
76 77
replace('aaaa','a','b')	replace('aaaa','aa','b')	replace('aaaa','a','bb')	replace('aaaa','','b')	replace('bbbb','a','c')
bbbb	bb	bbbbbbbb	aaaa	bbbb
78
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
79 80
replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL')
this is a REAL test
81
select soundex(''),soundex('he'),soundex('hello all folks');
82 83
soundex('')	soundex('he')	soundex('hello all folks')
	H000	H4142
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
select 'mood' sounds like 'mud';
'mood' sounds like 'mud'
1
select 'Glazgo' sounds like 'Liverpool';
'Glazgo' sounds like 'Liverpool'
0
select null sounds like 'null';
null sounds like 'null'
NULL
select 'null' sounds like null;
'null' sounds like null
NULL
select null sounds like null;
null sounds like null
NULL
99
select md5('hello');
100 101
md5('hello')
5d41402abc4b2a76b9719d911017c592
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
select sha('abc');
sha('abc')
a9993e364706816aba3e25717850c26c9cd0d89d
select sha1('abc');
sha1('abc')
a9993e364706816aba3e25717850c26c9cd0d89d
select aes_decrypt(aes_encrypt('abc','1'),'1');
aes_decrypt(aes_encrypt('abc','1'),'1')
abc
select aes_decrypt(aes_encrypt('abc','1'),1);
aes_decrypt(aes_encrypt('abc','1'),1)
abc
select aes_encrypt(NULL,"a");
aes_encrypt(NULL,"a")
NULL
select aes_encrypt("a",NULL);
aes_encrypt("a",NULL)
NULL
select aes_decrypt(NULL,"a");
aes_decrypt(NULL,"a")
NULL
select aes_decrypt("a",NULL);
aes_decrypt("a",NULL)
NULL
select aes_decrypt("a","a");
aes_decrypt("a","a")
NULL
select aes_decrypt(aes_encrypt("","a"),"a");
aes_decrypt(aes_encrypt("","a"),"a")

132
select repeat('monty',5),concat('*',space(5),'*');
133 134
repeat('monty',5)	concat('*',space(5),'*')
montymontymontymontymonty	*     *
135
select reverse('abc'),reverse('abcd');
136 137
reverse('abc')	reverse('abcd')
cba	dcba
138
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12');
139 140
rpad('a',4,'1')	rpad('a',4,'12')	rpad('abcd',3,'12')
a111	a121	abc
141
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12');
142 143
lpad('a',4,'1')	lpad('a',4,'12')	lpad('abcd',3,'12')
111a	121a	abc
144
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
145 146
rpad(741653838,17,'0')	lpad(741653838,17,'0')
74165383800000000	00000000741653838
147
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
148 149
rpad('abcd',7,'ab')	lpad('abcd',7,'ab')
abcdaba	abaabcd
150
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
151 152
rpad('abcd',1,'ab')	lpad('abcd',1,'ab')
a	a
153
select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
154 155
LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')	GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
HAROLD	HARRY
156
select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least(3,2,1)*1.0,greatest(1,1.1,1.0),least("10",9),greatest("A","B","0");
157 158
least(1,2,3) | greatest(16,32,8)	least(5,4)*1	greatest(-1.0,1.0)*1	least(3,2,1)*1.0	greatest(1,1.1,1.0)	least("10",9)	greatest("A","B","0")
33	4	1.0	1.0	1.1	9	B
159
select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
160 161
decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000)
1
162
select decode(encode("abcdef","monty"),"monty")="abcdef";
163 164
decode(encode("abcdef","monty"),"monty")="abcdef"
1
165 166
select quote('\'\"\\test');
quote('\'\"\\test')
ram@ram.(none)'s avatar
ram@ram.(none) committed
167
'\'"\\test'
168 169 170
select quote(concat('abc\'', '\\cba'));
quote(concat('abc\'', '\\cba'))
'abc\'\\cba'
171 172 173
select quote(1/0), quote('\0\Z');
quote(1/0)	quote('\0\Z')
NULL	'\0\Z'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
174 175
select length(quote(concat(char(0),"test")));
length(quote(concat(char(0),"test")))
ram@mysql.r18.ru's avatar
ram@mysql.r18.ru committed
176
8
177
select reverse("");
178 179
reverse("")

180
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
181 182
insert("aa",100,1,"b")	insert("aa",1,3,"b")	left("aa",-1)	substring("a",1,2)
aa	b		a
183
select elt(2,1),field(NULL,"a","b","c"),reverse("");
184 185
elt(2,1)	field(NULL,"a","b","c")	reverse("")
NULL	0	
186
select locate("a","b",2),locate("","a",1);
187 188
locate("a","b",2)	locate("","a",1)
0	1
189
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
190 191
ltrim("a")	rtrim("a")	trim(BOTH "" from "a")	trim(BOTH " " from "a")
a	a	a	a
192
select concat("1","2")|0,concat("1",".5")+0.0;
193 194
concat("1","2")|0	concat("1",".5")+0.0
12	1.5
195
select substring_index("www.tcx.se","",3);
196 197
substring_index("www.tcx.se","",3)

198
select length(repeat("a",100000000)),length(repeat("a",1000*64));
199 200
length(repeat("a",100000000))	length(repeat("a",1000*64))
NULL	64000
201
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
202 203
position("0" in "baaa" in (1))	position("0" in "1" in (1,2,3))	position("sql" in ("mysql"))
1	0	3
204
select position(("1" in (1,2,3)) in "01");
205 206
position(("1" in (1,2,3)) in "01")
2
207
select length(repeat("a",65500)),length(concat(repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",10000)))),length(insert(repeat("a",40000),1,30000,repeat("b",50000)));
208 209
length(repeat("a",65500))	length(concat(repeat("a",32000),repeat("a",32000)))	length(replace("aaaaa","a",concat(repeat("a",10000))))	length(insert(repeat("a",40000),1,30000,repeat("b",50000)))
65500	64000	50000	60000
210
select length(repeat("a",1000000)),length(concat(repeat("a",32000),repeat("a",32000),repeat("a",32000))),length(replace("aaaaa","a",concat(repeat("a",32000)))),length(insert(repeat("a",48000),1,1000,repeat("a",48000)));
211 212
length(repeat("a",1000000))	length(concat(repeat("a",32000),repeat("a",32000),repeat("a",32000)))	length(replace("aaaaa","a",concat(repeat("a",32000))))	length(insert(repeat("a",48000),1,1000,repeat("a",48000)))
1000000	96000	160000	95000
213 214 215
create table t1 ( domain char(50) );
insert into t1 VALUES ("hello.de" ), ("test.de" );
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@hello.de';
216 217
domain
hello.de
218
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
219 220
domain
test.de
221 222 223 224 225 226 227 228 229 230 231 232 233 234
drop table t1;
CREATE TABLE t1 (
id int(10) unsigned NOT NULL,
title varchar(255) default NULL,
prio int(10) unsigned default NULL,
category int(10) unsigned default NULL,
program int(10) unsigned default NULL,
bugdesc text,
created datetime default NULL,
modified timestamp(14) NOT NULL,
bugstatus int(10) unsigned default NULL,
submitter int(10) unsigned default NULL
) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
235 236
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1;
CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"')
237
"Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
238
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
239 240
CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
"Link";"1";"1";"1";"0";"4"
241 242
SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter) FROM t1;
CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter)
243
Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
244 245 246
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
bugdesc	REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb')
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
247
drop table t1;
248 249 250 251 252
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM;
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
1
DROP TABLE t1;
253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280
select 1=_latin1'1';
1=_latin1'1'
1
select _latin1'1'=1;
_latin1'1'=1
1
select _latin2'1'=1;
_latin2'1'=1
1
select 1=_latin2'1';
1=_latin2'1'
1
select _latin1'1'=_latin2'1';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
select row('a','b','c') = row('a','b','c');
row('a','b','c') = row('a','b','c')
1
select row('A','b','c') = row('a','b','c');
row('A','b','c') = row('a','b','c')
1
select row('A' COLLATE latin1_bin,'b','c') = row('a','b','c');
row('A' COLLATE latin1_bin,'b','c') = row('a','b','c')
0
select row('A','b','c') = row('a' COLLATE latin1_bin,'b','c');
row('A','b','c') = row('a' COLLATE latin1_bin,'b','c')
0
select row('A' COLLATE latin1_general_ci,'b','c') = row('a' COLLATE latin1_bin,'b','c');
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation '='
bar@bar.mysql.r18.ru's avatar
bar@bar.mysql.r18.ru committed
281 282 283 284 285 286
select concat(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat'
select concat(_latin1'a',_latin2'a',_latin5'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin5_turkish_ci,COERCIBLE) for operation 'concat'
select concat(_latin1'a',_latin2'a',_latin5'a',_latin7'a');
ERROR HY000: Illegal mix of collations for operation 'concat'
287 288 289 290 291 292 293 294 295 296 297 298 299
select FIELD('b','A','B');
FIELD('b','A','B')
2
select FIELD('B','A','B');
FIELD('B','A','B')
2
select FIELD('b' COLLATE latin1_bin,'A','B');
FIELD('b' COLLATE latin1_bin,'A','B')
0
select FIELD('b','A' COLLATE latin1_bin,'B');
FIELD('b','A' COLLATE latin1_bin,'B')
0
select FIELD(_latin2'b','A','B');
300
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
301
select FIELD('b',_latin2'A','B');
302
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
303 304 305
select FIELD('b',_latin2'A','B',1);
FIELD('b',_latin2'A','B',1)
1
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332
select POSITION(_latin1'B' IN _latin1'abcd');
POSITION(_latin1'B' IN _latin1'abcd')
2
select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin);
POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin)
0
select POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd');
POSITION(_latin1'B' COLLATE latin1_bin IN _latin1'abcd')
0
select POSITION(_latin1'B' COLLATE latin1_general_ci IN _latin1'abcd' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_general_ci,EXPLICIT) for operation 'locate'
select POSITION(_latin1'B' IN _latin2'abcd');
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'locate'
select FIND_IN_SET(_latin1'B',_latin1'a,b,c,d');
FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')
2
select FIND_IN_SET(_latin1'B' COLLATE latin1_general_ci,_latin1'a,b,c,d' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'find_in_set'
select FIND_IN_SET(_latin1'B',_latin2'a,b,c,d');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'find_in_set'
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2);
SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin1'd',2)
abcdabc
select SUBSTRING_INDEX(_latin1'abcdabcdabcd',_latin2'd',2);
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'substr_index'
select SUBSTRING_INDEX(_latin1'abcdabcdabcd' COLLATE latin1_general_ci,_latin1'd' COLLATE latin1_bin,2);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'substr_index'
333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352
select _latin1'B' between _latin1'a' and _latin1'c';
_latin1'B' between _latin1'a' and _latin1'c'
1
select _latin1'B' collate latin1_bin between _latin1'a' and _latin1'c';
_latin1'B' collate latin1_bin between _latin1'a' and _latin1'c'
0
select _latin1'B' between _latin1'a' collate latin1_bin and _latin1'c';
_latin1'B' between _latin1'a' collate latin1_bin and _latin1'c'
0
select _latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin;
_latin1'B' between _latin1'a' and _latin1'c' collate latin1_bin
0
select _latin2'B' between _latin1'a' and _latin1'b';
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
select _latin1'B' between _latin2'a' and _latin1'b';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'
select _latin1'B' between _latin1'a' and _latin2'b';
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation 'between'
select _latin1'B' collate latin1_general_ci between _latin1'a' collate latin1_bin and _latin1'b';
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation 'between'
353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374
select _latin1'B' in (_latin1'a',_latin1'b');
_latin1'B' in (_latin1'a',_latin1'b')
1
select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b');
_latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')
0
select _latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b');
_latin1'B' in (_latin1'a' collate latin1_bin,_latin1'b')
0
select _latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin);
_latin1'B' in (_latin1'a',_latin1'b' collate latin1_bin)
0
select _latin2'B' in (_latin1'a',_latin1'b');
ERROR HY000: Illegal mix of collations for operation ' IN '
select _latin1'B' in (_latin2'a',_latin1'b');
ERROR HY000: Illegal mix of collations for operation ' IN '
select _latin1'B' in (_latin1'a',_latin2'b');
ERROR HY000: Illegal mix of collations for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
ERROR HY000: Illegal mix of collations for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations for operation ' IN '
375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392
select collation(bin(130)), coercibility(bin(130));
collation(bin(130))	coercibility(bin(130))
latin1_swedish_ci	3
select collation(oct(130)), coercibility(oct(130));
collation(oct(130))	coercibility(oct(130))
latin1_swedish_ci	3
select collation(conv(130,16,10)), coercibility(conv(130,16,10));
collation(conv(130,16,10))	coercibility(conv(130,16,10))
latin1_swedish_ci	3
select collation(hex(130)), coercibility(hex(130));
collation(hex(130))	coercibility(hex(130))
latin1_swedish_ci	3
select collation(char(130)), coercibility(hex(130));
collation(char(130))	coercibility(hex(130))
binary	3
select collation(format(130,10)), coercibility(format(130,10));
collation(format(130,10))	coercibility(format(130,10))
latin1_swedish_ci	3
393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410
select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
collation(lcase(_latin2'a'))	coercibility(lcase(_latin2'a'))
latin2_general_ci	3
select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
collation(ucase(_latin2'a'))	coercibility(ucase(_latin2'a'))
latin2_general_ci	3
select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
collation(left(_latin2'a',1))	coercibility(left(_latin2'a',1))
latin2_general_ci	3
select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
collation(right(_latin2'a',1))	coercibility(right(_latin2'a',1))
latin2_general_ci	3
select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
collation(substring(_latin2'a',1,1))	coercibility(substring(_latin2'a',1,1))
latin2_general_ci	3
select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
collation(concat(_latin2'a',_latin2'b'))	coercibility(concat(_latin2'a',_latin2'b'))
latin2_general_ci	3
411 412 413 414 415 416
select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
collation(lpad(_latin2'a',4,_latin2'b'))	coercibility(lpad(_latin2'a',4,_latin2'b'))
binary	3
select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
collation(rpad(_latin2'a',4,_latin2'b'))	coercibility(rpad(_latin2'a',4,_latin2'b'))
latin2_general_ci	3
417 418 419
select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
collation(concat_ws(_latin2'a',_latin2'b'))	coercibility(concat_ws(_latin2'a',_latin2'b'))
latin2_general_ci	3
420 421 422 423 424 425
select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
collation(make_set(255,_latin2'a',_latin2'b',_latin2'c'))	coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'))
latin2_general_ci	3
select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
collation(export_set(255,_latin2'y',_latin2'n',_latin2' '))	coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '))
binary	3
426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443
select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
collation(trim(_latin2' a '))	coercibility(trim(_latin2' a '))
latin2_general_ci	3
select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
collation(ltrim(_latin2' a '))	coercibility(ltrim(_latin2' a '))
latin2_general_ci	3
select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
collation(rtrim(_latin2' a '))	coercibility(rtrim(_latin2' a '))
latin2_general_ci	3
select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
collation(trim(LEADING _latin2' ' FROM _latin2'a'))	coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))
latin2_general_ci	3
select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
collation(trim(TRAILING _latin2' ' FROM _latin2'a'))	coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))
latin2_general_ci	3
select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
collation(trim(BOTH _latin2' ' FROM _latin2'a'))	coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))
latin2_general_ci	3
444 445 446 447 448 449 450 451 452 453 454 455 456 457 458
select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
collation(repeat(_latin2'a',10))	coercibility(repeat(_latin2'a',10))
latin2_general_ci	3
select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
collation(reverse(_latin2'ab'))	coercibility(reverse(_latin2'ab'))
latin2_general_ci	3
select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
collation(quote(_latin2'ab'))	coercibility(quote(_latin2'ab'))
latin2_general_ci	3
select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
collation(soundex(_latin2'ab'))	coercibility(soundex(_latin2'ab'))
latin2_general_ci	3
select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
collation(substring(_latin2'ab',1))	coercibility(substring(_latin2'ab',1))
latin2_general_ci	3
459 460 461
select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
collation(insert(_latin2'abcd',2,3,_latin2'ef'))	coercibility(insert(_latin2'abcd',2,3,_latin2'ef'))
latin2_general_ci	3
462 463 464
select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
collation(replace(_latin2'abcd',_latin2'b',_latin2'B'))	coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'))
latin2_general_ci	3
465 466
create table t1 
select
467 468 469 470 471 472
bin(130),
oct(130),
conv(130,16,10),
hex(130),
char(130),
format(130,10),
473 474 475 476 477 478
left(_latin2'a',1),
right(_latin2'a',1), 
lcase(_latin2'a'), 
ucase(_latin2'a'),
substring(_latin2'a',1,1),
concat(_latin2'a',_latin2'b'),
479 480
lpad(_latin2'a',4,_latin2'b'),
rpad(_latin2'a',4,_latin2'b'),
481
concat_ws(_latin2'a',_latin2'b'),
482 483
make_set(255,_latin2'a',_latin2'b',_latin2'c'),
export_set(255,_latin2'y',_latin2'n',_latin2' '),
484 485 486 487 488
trim(_latin2' a '),
ltrim(_latin2' a '),
rtrim(_latin2' a '),
trim(LEADING _latin2' ' FROM _latin2' a '),
trim(TRAILING _latin2' ' FROM _latin2' a '),
489 490 491 492 493
trim(BOTH _latin2' ' FROM _latin2' a '),
repeat(_latin2'a',10),
reverse(_latin2'ab'),
quote(_latin2'ab'),
soundex(_latin2'ab'),
494
substring(_latin2'ab',1),
495 496
insert(_latin2'abcd',2,3,_latin2'ef'),
replace(_latin2'abcd',_latin2'b',_latin2'B')
497
;
498 499
Warnings:
Warning	1263	Data truncated for column 'format(130,10)' at row 1
500 501 502
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
503 504 505 506 507 508
  `bin(130)` char(64) NOT NULL default '',
  `oct(130)` char(64) NOT NULL default '',
  `conv(130,16,10)` char(64) NOT NULL default '',
  `hex(130)` char(6) NOT NULL default '',
  `char(130)` char(1) NOT NULL default '',
  `format(130,10)` char(4) NOT NULL default '',
509 510 511 512 513 514
  `left(_latin2'a',1)` char(1) character set latin2 NOT NULL default '',
  `right(_latin2'a',1)` char(1) character set latin2 NOT NULL default '',
  `lcase(_latin2'a')` char(1) character set latin2 NOT NULL default '',
  `ucase(_latin2'a')` char(1) character set latin2 NOT NULL default '',
  `substring(_latin2'a',1,1)` char(1) character set latin2 NOT NULL default '',
  `concat(_latin2'a',_latin2'b')` char(2) character set latin2 NOT NULL default '',
515 516
  `lpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '',
  `rpad(_latin2'a',4,_latin2'b')` char(4) character set latin2 NOT NULL default '',
517
  `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '',
518 519
  `make_set(255,_latin2'a',_latin2'b',_latin2'c')` char(5) character set latin2 NOT NULL default '',
  `export_set(255,_latin2'y',_latin2'n',_latin2' ')` char(127) character set latin2 NOT NULL default '',
520 521 522 523 524
  `trim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
  `ltrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
  `rtrim(_latin2' a ')` char(3) character set latin2 NOT NULL default '',
  `trim(LEADING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
  `trim(TRAILING _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
525 526 527 528 529
  `trim(BOTH _latin2' ' FROM _latin2' a ')` char(3) character set latin2 NOT NULL default '',
  `repeat(_latin2'a',10)` char(10) character set latin2 NOT NULL default '',
  `reverse(_latin2'ab')` char(2) character set latin2 NOT NULL default '',
  `quote(_latin2'ab')` char(6) character set latin2 NOT NULL default '',
  `soundex(_latin2'ab')` char(4) character set latin2 NOT NULL default '',
530
  `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '',
531 532
  `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '',
  `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default ''
533 534
) TYPE=MyISAM CHARSET=latin1
drop table t1;
535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555
select SUBSTR('abcdefg',3,2);
SUBSTR('abcdefg',3,2)
cd
select SUBSTRING('abcdefg',3,2);
SUBSTRING('abcdefg',3,2)
cd
select SUBSTR('abcdefg',-3,2) FROM DUAL;
SUBSTR('abcdefg',-3,2)
ef
select SUBSTR('abcdefg',-1,5) FROM DUAL;
SUBSTR('abcdefg',-1,5)
g
select SUBSTR('abcdefg',0,0) FROM DUAL;
SUBSTR('abcdefg',0,0)

select SUBSTR('abcdefg',-1,-1) FROM DUAL;
SUBSTR('abcdefg',-1,-1)

select SUBSTR('abcdefg',1,-1) FROM DUAL;
SUBSTR('abcdefg',1,-1)