func_str.result 37.6 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 16 17 18 19 20 21 22 23
select char_length('\n\t\r\b\0\_\%\\');
char_length('\n\t\r\b\0\_\%\\')
10
select length(_latin1'\n\t\n\b\0\\_\\%\\');
length(_latin1'\n\t\n\b\0\\_\\%\\')
10
select concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h');
concat('monty',' was here ','again')	length('hello')	char(ascii('h'))	ord('h')
monty was here again	5	h	104
24
select locate('he','hello'),locate('he','hello',2),locate('lo','hello',2) ;
25 26
locate('he','hello')	locate('he','hello',2)	locate('lo','hello',2)
1	0	4
27 28 29 30 31
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')
32
3	0
33
select left('hello',2),right('hello',2),substring('hello',2,2),mid('hello',1,5) ;
34 35
left('hello',2)	right('hello',2)	substring('hello',2,2)	mid('hello',1,5)
he	lo	el	hello
36
select concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1)) ;
37 38
concat('',left(right(concat('what ',concat('is ','happening')),9),4),'',substring('monty',5,1))
happy
39
select substring_index('www.tcx.se','.',-2),substring_index('www.tcx.se','.',1);
40 41
substring_index('www.tcx.se','.',-2)	substring_index('www.tcx.se','.',1)
tcx.se	www
42
select substring_index('www.tcx.se','tcx',1),substring_index('www.tcx.se','tcx',-1);
43 44
substring_index('www.tcx.se','tcx',1)	substring_index('www.tcx.se','tcx',-1)
www.	.se
45
select substring_index('.tcx.se','.',-2),substring_index('.tcx.se','.tcx',-1);
46 47
substring_index('.tcx.se','.',-2)	substring_index('.tcx.se','.tcx',-1)
tcx.se	.se
48
select concat(':',ltrim('  left  '),':',rtrim('  right  '),':');
49 50
concat(':',ltrim('  left  '),':',rtrim('  right  '),':')
:left  :  right:
51
select concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':');
52 53
concat(':',trim(LEADING FROM ' left'),':',trim(TRAILING FROM ' right '),':')
:left: right:
54
select concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':');
55 56
concat(':',trim(' m '),':',trim(BOTH FROM ' y '),':',trim('*' FROM '*s*'),':')
:m:y:s:
57
select concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':');
58 59
concat(':',trim(BOTH 'ab' FROM 'ababmyabab'),':',trim(BOTH '*' FROM '***sql'),':')
:my:sql:
60
select concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':');
61 62
concat(':',trim(LEADING '.*' FROM '.*my'),':',trim(TRAILING '.*' FROM 'sql.*.*'),':')
:my:sql:
63
select TRIM("foo" FROM "foo"), TRIM("foo" FROM "foook"), TRIM("foo" FROM "okfoo");
64 65
TRIM("foo" FROM "foo")	TRIM("foo" FROM "foook")	TRIM("foo" FROM "okfoo")
	ok	ok
66
select concat_ws(', ','monty','was here','again');
67 68
concat_ws(', ','monty','was here','again')
monty, was here, again
69
select concat_ws(NULL,'a'),concat_ws(',',NULL,'');
70 71
concat_ws(NULL,'a')	concat_ws(',',NULL,'')
NULL	
72
select concat_ws(',','',NULL,'a');
73
concat_ws(',','',NULL,'a')
74
,a
75
SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"');
76 77
CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('d',100)), '"')
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
78
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
79 80
insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
this	is a	test
81
select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
82 83
replace('aaaa','a','b')	replace('aaaa','aa','b')	replace('aaaa','a','bb')	replace('aaaa','','b')	replace('bbbb','a','c')
bbbb	bb	bbbbbbbb	aaaa	bbbb
84
select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ;
85 86
replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL')
this is a REAL test
serg@serg.mylan's avatar
serg@serg.mylan committed
87 88 89
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
soundex('')	soundex('he')	soundex('hello all folks')	soundex('#3556 in bugdb')
	H000	H4142	I51231
90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
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
105
select md5('hello');
106 107
md5('hello')
5d41402abc4b2a76b9719d911017c592
108 109 110
select crc32("123");
crc32("123")
2286445522
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140
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")

141
select repeat('monty',5),concat('*',space(5),'*');
142 143
repeat('monty',5)	concat('*',space(5),'*')
montymontymontymontymonty	*     *
144
select reverse('abc'),reverse('abcd');
145 146
reverse('abc')	reverse('abcd')
cba	dcba
147 148 149 150 151 152
select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'), rpad(11, 10 , 22), rpad("ab", 10, 22);
rpad('a',4,'1')	rpad('a',4,'12')	rpad('abcd',3,'12')	rpad(11, 10 , 22)	rpad("ab", 10, 22)
a111	a121	abc	1122222222	ab22222222
select lpad('a',4,'1'),lpad('a',4,'12'),lpad('abcd',3,'12'), lpad(11, 10 , 22);
lpad('a',4,'1')	lpad('a',4,'12')	lpad('abcd',3,'12')	lpad(11, 10 , 22)
111a	121a	abc	2222222211
153
select rpad(741653838,17,'0'),lpad(741653838,17,'0');
154 155
rpad(741653838,17,'0')	lpad(741653838,17,'0')
74165383800000000	00000000741653838
156
select rpad('abcd',7,'ab'),lpad('abcd',7,'ab');
157 158
rpad('abcd',7,'ab')	lpad('abcd',7,'ab')
abcdaba	abaabcd
159
select rpad('abcd',1,'ab'),lpad('abcd',1,'ab');
160 161
rpad('abcd',1,'ab')	lpad('abcd',1,'ab')
a	a
162 163 164 165 166 167
select rpad('STRING', 20, CONCAT('p','a','d') );
rpad('STRING', 20, CONCAT('p','a','d') )
STRINGpadpadpadpadpa
select lpad('STRING', 20, CONCAT('p','a','d') );
lpad('STRING', 20, CONCAT('p','a','d') )
padpadpadpadpaSTRING
168
select LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD'),GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD');
169 170
LEAST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')	GREATEST(NULL,'HARRY','HARRIOT',NULL,'HAROLD')
HAROLD	HARRY
171
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");
172 173
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
174
select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000);
175 176
decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000)
1
177
select decode(encode("abcdef","monty"),"monty")="abcdef";
178 179
decode(encode("abcdef","monty"),"monty")="abcdef"
1
180 181
select quote('\'\"\\test');
quote('\'\"\\test')
ram@ram.(none)'s avatar
ram@ram.(none) committed
182
'\'"\\test'
183 184 185
select quote(concat('abc\'', '\\cba'));
quote(concat('abc\'', '\\cba'))
'abc\'\\cba'
186 187 188
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
189 190
select length(quote(concat(char(0),"test")));
length(quote(concat(char(0),"test")))
ram@mysql.r18.ru's avatar
ram@mysql.r18.ru committed
191
8
192 193 194
select hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))));
hex(quote(concat(char(224),char(227),char(230),char(231),char(232),char(234),char(235))))
27E0E3E6E7E8EAEB27
195 196 197
select unhex(hex("foobar")), hex(unhex("1234567890ABCDEF")), unhex("345678"), unhex(NULL);
unhex(hex("foobar"))	hex(unhex("1234567890ABCDEF"))	unhex("345678")	unhex(NULL)
foobar	1234567890ABCDEF	4Vx	NULL
serg@serg.mylan's avatar
serg@serg.mylan committed
198 199 200 201 202 203
select hex(unhex("1")), hex(unhex("12")), hex(unhex("123")), hex(unhex("1234")), hex(unhex("12345")), hex(unhex("123456"));
hex(unhex("1"))	hex(unhex("12"))	hex(unhex("123"))	hex(unhex("1234"))	hex(unhex("12345"))	hex(unhex("123456"))
01	12	0123	1234	012345	123456
select length(unhex(md5("abrakadabra")));
length(unhex(md5("abrakadabra")))
16
204 205 206
select concat('a', quote(NULL));
concat('a', quote(NULL))
aNULL
207
select reverse("");
208 209
reverse("")

210
select insert("aa",100,1,"b"),insert("aa",1,3,"b"),left("aa",-1),substring("a",1,2);
211 212
insert("aa",100,1,"b")	insert("aa",1,3,"b")	left("aa",-1)	substring("a",1,2)
aa	b		a
213
select elt(2,1),field(NULL,"a","b","c"),reverse("");
214 215
elt(2,1)	field(NULL,"a","b","c")	reverse("")
NULL	0	
216
select locate("a","b",2),locate("","a",1);
217 218
locate("a","b",2)	locate("","a",1)
0	1
219
select ltrim("a"),rtrim("a"),trim(BOTH "" from "a"),trim(BOTH " " from "a");
220 221
ltrim("a")	rtrim("a")	trim(BOTH "" from "a")	trim(BOTH " " from "a")
a	a	a	a
222
select concat("1","2")|0,concat("1",".5")+0.0;
223 224
concat("1","2")|0	concat("1",".5")+0.0
12	1.5
225
select substring_index("www.tcx.se","",3);
226 227
substring_index("www.tcx.se","",3)

228
select length(repeat("a",100000000)),length(repeat("a",1000*64));
229 230
length(repeat("a",100000000))	length(repeat("a",1000*64))
NULL	64000
231
Warnings:
232
Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
233
select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"));
234 235
position("0" in "baaa" in (1))	position("0" in "1" in (1,2,3))	position("sql" in ("mysql"))
1	0	3
236
select position(("1" in (1,2,3)) in "01");
237 238
position(("1" in (1,2,3)) in "01")
2
239
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)));
240 241
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
242
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)));
243 244
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
245 246 247
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';
248 249
domain
hello.de
250
select domain from t1 where concat('@', trim(leading '.' from concat('.', domain))) = '@test.de';
251 252
domain
test.de
253 254 255 256 257 258 259 260 261 262 263 264
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
265
) ENGINE=MyISAM;
266
INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4);
267 268
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), '"')
269
"Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4"
270
SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"') FROM t1;
271 272
CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugstatus,submitter), '"')
"Link";"1";"1";"1";"0";"4"
273 274
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)
275
Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4
276 277 278
SELECT bugdesc, REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb') from t1 group by bugdesc;
bugdesc	REPLACE(bugdesc, 'xxxxxxxxxxxxxxxxxxxx', 'bbbbbbbbbbbbbbbbbbbb')
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
279
drop table t1;
monty@mysql.com's avatar
monty@mysql.com committed
280 281 282 283 284 285 286 287 288
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf');
SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password");
1
DROP TABLE t1;
CREATE TABLE t1 (
wid int(10) unsigned NOT NULL auto_increment,
data_podp date default NULL,
status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy',
monty@mysql.com's avatar
monty@mysql.com committed
289
PRIMARY KEY(wid)
monty@mysql.com's avatar
monty@mysql.com committed
290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312
);
INSERT INTO t1 VALUES (8,NULL,'real');
INSERT INTO t1 VALUES (9,NULL,'nowy');
SELECT elt(status_wnio,data_podp) FROM t1 GROUP BY wid;
elt(status_wnio,data_podp)
NULL
NULL
DROP TABLE t1;
CREATE TABLE t1 (title text) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education');
INSERT INTO t1 VALUES ('House passes the CAREERS bill');
SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1;
CONCAT("</a>",RPAD("",(55 - LENGTH(title)),"."))
NULL
</a>..........................
DROP TABLE t1;
CREATE TABLE t1 (i int, j int);
INSERT INTO t1 VALUES (1,1),(2,2);
SELECT DISTINCT i, ELT(j, '345', '34') FROM t1;
i	ELT(j, '345', '34')
1	345
2	34
DROP TABLE t1;
monty@mysql.com's avatar
monty@mysql.com committed
313 314 315 316 317
create table t1(a char(4));
insert into t1 values ('one'),(NULL),('two'),('four');
select a, quote(a), isnull(quote(a)), quote(a) is null, ifnull(quote(a), 'n') from t1;
a	quote(a)	isnull(quote(a))	quote(a) is null	ifnull(quote(a), 'n')
one	'one'	0	0	'one'
318
NULL	NULL	0	0	NULL
monty@mysql.com's avatar
monty@mysql.com committed
319 320 321
two	'two'	0	0	'two'
four	'four'	0	0	'four'
drop table t1;
322 323 324 325 326
select trim(trailing 'foo' from 'foo');
trim(trailing 'foo' from 'foo')

select trim(leading 'foo' from 'foo');
trim(leading 'foo' from 'foo')
monty@mysql.com's avatar
monty@mysql.com committed
327

monty@mysql.com's avatar
monty@mysql.com committed
328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361
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 '='
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'
362 363
select concat_ws(_latin1'a',_latin2'a');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation 'concat_ws'
monty@mysql.com's avatar
monty@mysql.com committed
364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
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');
ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
select FIELD('b',_latin2'A','B');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field'
select FIELD('b',_latin2'A','B',1);
FIELD('b',_latin2'A','B',1)
1
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'
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'
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 (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' in (_latin2'a',_latin1'b');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' in (_latin1'a',_latin2'b');
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE) for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a' COLLATE latin1_bin,_latin1'b');
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_bin,EXPLICIT), (latin1_swedish_ci,COERCIBLE) for operation ' IN '
select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '
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))
466
latin1_swedish_ci	3
monty@mysql.com's avatar
monty@mysql.com committed
467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
select collation(format(130,10)), coercibility(format(130,10));
collation(format(130,10))	coercibility(format(130,10))
latin1_swedish_ci	3
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
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'))
latin2_general_ci	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
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
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
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
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
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
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
542 543 544
select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
collation(encode('abcd','ab'))	coercibility(encode('abcd','ab'))
binary	3
monty@mysql.com's avatar
monty@mysql.com committed
545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575
create table t1 
select
bin(130),
oct(130),
conv(130,16,10),
hex(130),
char(130),
format(130,10),
left(_latin2'a',1),
right(_latin2'a',1), 
lcase(_latin2'a'), 
ucase(_latin2'a'),
substring(_latin2'a',1,1),
concat(_latin2'a',_latin2'b'),
lpad(_latin2'a',4,_latin2'b'),
rpad(_latin2'a',4,_latin2'b'),
concat_ws(_latin2'a',_latin2'b'),
make_set(255,_latin2'a',_latin2'b',_latin2'c'),
export_set(255,_latin2'y',_latin2'n',_latin2' '),
trim(_latin2' a '),
ltrim(_latin2' a '),
rtrim(_latin2' a '),
trim(LEADING _latin2' ' FROM _latin2' a '),
trim(TRAILING _latin2' ' FROM _latin2' a '),
trim(BOTH _latin2' ' FROM _latin2' a '),
repeat(_latin2'a',10),
reverse(_latin2'ab'),
quote(_latin2'ab'),
soundex(_latin2'ab'),
substring(_latin2'ab',1),
insert(_latin2'abcd',2,3,_latin2'ef'),
576 577
replace(_latin2'abcd',_latin2'b',_latin2'B'),
encode('abcd','ab')
monty@mysql.com's avatar
monty@mysql.com committed
578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612
;
Warnings:
Warning	1265	Data truncated for column 'format(130,10)' at row 1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `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 '',
  `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 '',
  `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 '',
  `concat_ws(_latin2'a',_latin2'b')` char(1) character set latin2 NOT NULL default '',
  `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 '',
  `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 '',
  `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 '',
  `substring(_latin2'ab',1)` char(2) character set latin2 NOT NULL default '',
  `insert(_latin2'abcd',2,3,_latin2'ef')` char(6) character set latin2 NOT NULL default '',
613 614
  `replace(_latin2'abcd',_latin2'b',_latin2'B')` char(4) character set latin2 NOT NULL default '',
  `encode('abcd','ab')` binary(4) NOT NULL default ''
monty@mysql.com's avatar
monty@mysql.com committed
615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
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)

create table t7 (s1 char);
select * from t7
where concat(s1 collate latin1_general_ci,s1 collate latin1_swedish_ci) = 'AA';
ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat'
drop table t7;
select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2);
substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)	substring_index("1abcd;2abcd;3abcd;4abcd", ';', -2)
1abcd;2abcd	3abcd;4abcd
explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'),  concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
650
Note	1003	select md5(_latin1'hello') AS `md5('hello')`,sha(_latin1'abc') AS `sha('abc')`,sha(_latin1'abc') AS `sha1('abc')`,soundex(_latin1'') AS `soundex('')`,(soundex(_latin1'mood') = soundex(_latin1'mud')) AS `'mood' sounds like 'mud'`,aes_decrypt(aes_encrypt(_latin1'abc',_latin1'1'),_latin1'1') AS `aes_decrypt(aes_encrypt('abc','1'),'1')`,concat(_latin1'*',repeat(_latin1' ',5),_latin1'*') AS `concat('*',space(5),'*')`,reverse(_latin1'abc') AS `reverse('abc')`,rpad(_latin1'a',4,_latin1'1') AS `rpad('a',4,'1')`,lpad(_latin1'a',4,_latin1'1') AS `lpad('a',4,'1')`,concat_ws(_latin1',',_latin1'',NULL,_latin1'a') AS `concat_ws(',','',NULL,'a')`,make_set(255,_latin2'a',_latin2'b',_latin2'c') AS `make_set(255,_latin2'a',_latin2'b',_latin2'c')`,elt(2,1) AS `elt(2,1)`,locate(_latin1'a',_latin1'b',2) AS `locate("a","b",2)`,format(130,10) AS `format(130,10)`,char(0) AS `char(0)`,conv(130,16,10) AS `conv(130,16,10)`,hex(130) AS `hex(130)`,cast(_latin1'HE' as char charset binary) AS `binary 'HE'`,export_set(255,_latin2'y',_latin2'n',_latin2' ') AS `export_set(255,_latin2'y',_latin2'n',_latin2' ')`,field((_latin1'b' collate _latin1'latin1_bin'),_latin1'A',_latin1'B') AS `FIELD('b' COLLATE latin1_bin,'A','B')`,find_in_set(_latin1'B',_latin1'a,b,c,d') AS `FIND_IN_SET(_latin1'B',_latin1'a,b,c,d')`,collation(conv(130,16,10)) AS `collation(conv(130,16,10))`,coercibility(conv(130,16,10)) AS `coercibility(conv(130,16,10))`,length(_latin1'\n	\r\0\\_\\%\\') AS `length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,bit_length(_latin1'\n	\r\0\\_\\%\\') AS `bit_length('\n\t\r\b\0\_\%\\')`,concat(_latin1'monty',_latin1' was here ',_latin1'again') AS `concat('monty',' was here ','again')`,length(_latin1'hello') AS `length('hello')`,char(ascii(_latin1'h')) AS `char(ascii('h'))`,ord(_latin1'h') AS `ord('h')`,quote((1 / 0)) AS `quote(1/0)`,crc32(_latin1'123') AS `crc32("123")`,replace(_latin1'aaaa',_latin1'a',_latin1'b') AS `replace('aaaa','a','b')`,insert(_latin1'txs',2,1,_latin1'hi') AS `insert('txs',2,1,'hi')`,left(_latin2'a',1) AS `left(_latin2'a',1)`,right(_latin2'a',1) AS `right(_latin2'a',1)`,lcase(_latin2'a') AS `lcase(_latin2'a')`,ucase(_latin2'a') AS `ucase(_latin2'a')`,substr(_latin1'abcdefg',3,2) AS `SUBSTR('abcdefg',3,2)`,substr_index(_latin1'1abcd;2abcd;3abcd;4abcd',_latin1';',2) AS `substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2)`,trim(_latin2' a ') AS `trim(_latin2' a ')`,ltrim(_latin2' a ') AS `ltrim(_latin2' a ')`,rtrim(_latin2' a ') AS `rtrim(_latin2' a ')`,decode(encode(repeat(_latin1'a',100000))) AS `decode(encode(repeat("a",100000),"monty"),"monty")`
monty@mysql.com's avatar
monty@mysql.com committed
651 652 653
SELECT lpad(12345, 5, "#");
lpad(12345, 5, "#")
12345
654 655 656
SELECT conv(71, 10, 36), conv('1Z', 36, 10);
conv(71, 10, 36)	conv('1Z', 36, 10)
1Z	71
657 658 659 660 661 662 663 664 665 666
create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
insert into t1 values (1,'aaaaaaaaaa'), (2,'bbbbbbbbbb');
create table t2 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 
where t2.id=t1.id order by name;
name
aaaaaaaaaaccccc
bbbbbbbbbbddddd
drop table t1, t2;
monty@mysql.com's avatar
monty@mysql.com committed
667 668
create table t1 (c1 INT, c2 INT UNSIGNED);
insert into t1 values ('21474836461','21474836461');
669 670
Warnings:
Warning	1265	Data truncated for column 'c1' at row 1
monty@mysql.com's avatar
monty@mysql.com committed
671 672 673 674 675
Warning	1265	Data truncated for column 'c2' at row 1
insert into t1 values ('-21474836461','-21474836461');
Warnings:
Warning	1265	Data truncated for column 'c1' at row 1
Warning	1265	Data truncated for column 'c2' at row 1
676 677 678
show warnings;
Level	Code	Message
Warning	1265	Data truncated for column 'c1' at row 1
monty@mysql.com's avatar
monty@mysql.com committed
679
Warning	1265	Data truncated for column 'c2' at row 1
680
select * from t1;
monty@mysql.com's avatar
monty@mysql.com committed
681 682 683
c1	c2
2147483647	4294967295
-2147483648	0
684
drop table t1;
685 686 687
select left(1234, 3) + 0;
left(1234, 3) + 0
123
688
create table t1 (a int not null primary key, b varchar(40), c datetime);
689
insert into t1 (a,b,c) values (1,'Tom','2004-12-10 12:13:14'),(2,'ball games','2004-12-10 12:13:14'), (3,'Basil','2004-12-10 12:13:14'), (4,'Dean','2004-12-10 12:13:14'),(5,'Ellis','2004-12-10 12:13:14'), (6,'Serg','2004-12-10 12:13:14'), (7,'Sergei','2004-12-10 12:13:14'),(8,'Georg','2004-12-10 12:13:14'),(9,'Salle','2004-12-10 12:13:14'),(10,'Sinisa','2004-12-10 12:13:14');
690 691 692 693
select count(*) as total, left(c,10) as reg from t1 group by reg order by reg desc limit 0,12;
total	reg
10	2004-12-10
drop table t1;
694 695 696 697 698 699
select quote(ltrim(concat('    ', 'a')));
quote(ltrim(concat('    ', 'a')))
'a'
select quote(trim(concat('    ', 'a')));
quote(trim(concat('    ', 'a')))
'a'
700 701 702 703 704 705 706
CREATE TABLE t1 SELECT 1 UNION SELECT 2 UNION SELECT 3;
SELECT QUOTE('A') FROM t1;
QUOTE('A')
'A'
'A'
'A'
DROP TABLE t1;
timour@mysql.com's avatar
timour@mysql.com committed
707 708 709 710 711 712
select trim(null from 'kate') as "must_be_null";
must_be_null
NULL
select trim('xyz' from null) as "must_be_null";
must_be_null
NULL
713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
a bigint(20) unsigned default NULL,
PRIMARY KEY  (id)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('0','16307858876001849059');
SELECT CONV('e251273eb74a8ee3', 16, 10);
CONV('e251273eb74a8ee3', 16, 10)
16307858876001849059
EXPLAIN 
SELECT id
FROM t1
WHERE a = 16307858876001849059;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
EXPLAIN 
SELECT id
FROM t1
WHERE a = CONV('e251273eb74a8ee3', 16, 10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
DROP TABLE t1;