1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
# name: test/sql/collate/collate_like.test
# description: Test collations in the LIKE clause
# group: [collate]
statement ok
PRAGMA enable_verification
query I
SELECT 'a' LIKE 'A' COLLATE NOCASE
----
true
query I
SELECT 'a' NOT LIKE 'A' COLLATE NOCASE
----
false
query I
SELECT 'A' COLLATE NOCASE LIKE 'a'
----
true
# like optimization rules
query I
SELECT 'a' LIKE 'A%' COLLATE NOCASE
----
true
query I
SELECT 'A' COLLATE NOCASE LIKE '%A' COLLATE NOCASE
----
true
query I
SELECT 'a' COLLATE NOCASE LIKE '%A%' COLLATE NOCASE
----
true
query I
SELECT 'OX' COLLATE NOACCENT.NOCASE LIKE 'ö%'
----
true
#### Testing LIKE with collated columns
statement ok
CREATE TABLE t1 (c1 VARCHAR, pattern VARCHAR)
statement ok
INSERT INTO t1 VALUES('A', 'a'),('a', 'A'),('AAAA', 'AaAa'),('aaaa', 'baba')
query I
SELECT c1 FROM t1 WHERE c1 LIKE pattern
----
query I
SELECT c1 FROM t1 WHERE c1 LIKE pattern COLLATE NOCASE
----
A
a
AAAA
# like escape
query I
SELECT 'a%ö' COLLATE NOACCENT LIKE 'a$%ö' ESCAPE '$'
----
true
query I
SELECT 'a%ö' COLLATE NOACCENT NOT LIKE 'a$%ö' ESCAPE '$'
----
false
# ilike
query I
SELECT 'oX' ILIKE 'Ö%'
----
false
query I
SELECT 'OX' COLLATE NOACCENT ILIKE 'ö%'
----
true
query I
SELECT 'öX' COLLATE NOACCENT NOT ILIKE 'Ö%'
----
false
# glob
query I
SELECT 'oX' GLOB 'O*'
----
false
query I
SELECT 'oX' COLLATE NOCASE GLOB 'O*'
----
true
|
