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
99
100
101
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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
SET sql_mode=ORACLE;
#
# MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
#
SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM(' '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
TRIM('abc') TRIM('abc ')||'.' '.'||TRIM(' abc ')||'.' TRIM(' ') TRIM(NULL) TRIM(SPACE(0)) TRIM(SPACE(10))
abc abc. .abc. NULL NULL NULL NULL
SELECT TRIM(TRAILING 'abc' FROM 'abc');
TRIM(TRAILING 'abc' FROM 'abc')
NULL
SELECT TRIM(TRAILING 'abc' FROM 'abc ');
TRIM(TRAILING 'abc' FROM 'abc ')
abc
SELECT TRIM(TRAILING 'abc' FROM ' abc');
TRIM(TRAILING 'abc' FROM ' abc')
SELECT TRIM(LEADING 'abc' FROM 'abc');
TRIM(LEADING 'abc' FROM 'abc')
NULL
SELECT TRIM(LEADING 'abc' FROM 'abc ');
TRIM(LEADING 'abc' FROM 'abc ')
SELECT TRIM(LEADING 'abc' FROM ' abc');
TRIM(LEADING 'abc' FROM ' abc')
abc
SELECT TRIM(BOTH 'abc' FROM 'abc');
TRIM(BOTH 'abc' FROM 'abc')
NULL
SELECT TRIM(BOTH 'abc' FROM 'abc ');
TRIM(BOTH 'abc' FROM 'abc ')
SELECT TRIM(BOTH 'abc' FROM ' abc');
TRIM(BOTH 'abc' FROM ' abc')
SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM(' '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
RTRIM('abc') RTRIM('abc ')||'.' RTRIM(' abc ')||'.' RTRIM(' ') RTRIM(NULL) RTRIM(SPACE(0)) RTRIM(SPACE(10))
abc abc. abc. NULL NULL NULL NULL
SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM(' '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
LTRIM('abc') LTRIM('abc ') LTRIM(' abc ') LTRIM(' ') LTRIM(NULL) LTRIM(SPACE(0)) LTRIM(SPACE(10))
abc abc abc NULL NULL NULL NULL
CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
INSERT INTO t1 VALUES ('abc',1);
INSERT INTO t1 VALUES (SPACE(0),2);
INSERT INTO t1 VALUES ('',3);
INSERT INTO t1 VALUES (' ',4);
INSERT INTO t1 VALUES (' ',5);
INSERT INTO t1 VALUES (' a ',6);
INSERT INTO t1 VALUES ('aa',7);
INSERT INTO t1 VALUES ('aabb',8);
INSERT INTO t1 VALUES ('bbaa',9);
INSERT INTO t1 VALUES ('aabbaa',10);
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
ord '['||c1||']' '.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.'
1 [abc] .bc.
2 [] .NULL.
3 [] .NULL.
4 [ ] . .
5 [ ] . .
6 [ a ] . a .
7 [aa] .NULL.
8 [aabb] .bb.
9 [bbaa] .bbaa.
10 [aabbaa] .bbaa.
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
ord '['||c1||']' '.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.'
1 [abc] .abc.
2 [] .NULL.
3 [] .NULL.
4 [ ] . .
5 [ ] . .
6 [ a ] . a .
7 [aa] .NULL.
8 [aabb] .aabb.
9 [bbaa] .bb.
10 [aabbaa] .aabb.
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
ord '['||c1||']' '.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.'
1 [abc] .bc.
2 [] .NULL.
3 [] .NULL.
4 [ ] . .
5 [ ] . .
6 [ a ] . a .
7 [aa] .NULL.
8 [aabb] .bb.
9 [bbaa] .bb.
10 [aabbaa] .bb.
SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
ord '['||c1||']' COALESCE(LTRIM(c1),'NULL')
1 [abc] abc
2 [] NULL
3 [] NULL
4 [ ] NULL
5 [ ] NULL
6 [ a ] a
7 [aa] aa
8 [aabb] aabb
9 [bbaa] bbaa
10 [aabbaa] aabbaa
SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
ord '['||c1||']' COALESCE(RTRIM(c1),'NULL')||'.'
1 [abc] abc.
2 [] NULL.
3 [] NULL.
4 [ ] NULL.
5 [ ] NULL.
6 [ a ] a.
7 [aa] aa.
8 [aabb] aabb.
9 [bbaa] bbaa.
10 [aabbaa] aabbaa.
EXPLAIN EXTENDED SELECT TRIM('abc'),
TRIM(BOTH 'a' FROM 'abc'),
TRIM(LEADING 'a' FROM 'abc'),
TRIM(TRAILING 'a' FROM 'abc') ;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select trim_oracle('abc') AS "TRIM('abc')",trim_oracle(both 'a' from 'abc') AS "TRIM(BOTH 'a' FROM 'abc')",trim_oracle(leading 'a' from 'abc') AS "TRIM(LEADING 'a' FROM 'abc')",trim_oracle(trailing 'a' from 'abc') AS "TRIM(TRAILING 'a' FROM 'abc')"
EXPLAIN EXTENDED SELECT RTRIM('abc'),
LTRIM('abc');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')"
CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
'['||c1||']',
TRIM(LEADING 'a' FROM c1),
TRIM(TRAILING 'a' FROM c1),
TRIM(BOTH 'a' FROM c1),
LTRIM(c1),
RTRIM(c1)
FROM t1 ORDER BY ord ;
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE VIEW "v1" AS select "t1"."ord" AS "ord",trim_oracle('abc') AS "TRIM('abc')",rtrim_oracle('abc') AS "RTRIM('abc')",ltrim_oracle('abc') AS "LTRIM('abc')",concat_operator_oracle(concat_operator_oracle('[',"t1"."c1"),']') AS "'['||c1||']'",trim_oracle(leading 'a' from "t1"."c1") AS "TRIM(LEADING 'a' FROM c1)",trim_oracle(trailing 'a' from "t1"."c1") AS "TRIM(TRAILING 'a' FROM c1)",trim_oracle(both 'a' from "t1"."c1") AS "TRIM(BOTH 'a' FROM c1)",ltrim_oracle("t1"."c1") AS "LTRIM(c1)",rtrim_oracle("t1"."c1") AS "RTRIM(c1)" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci
SELECT * FROM v1;
ord TRIM('abc') RTRIM('abc') LTRIM('abc') '['||c1||']' TRIM(LEADING 'a' FROM c1) TRIM(TRAILING 'a' FROM c1) TRIM(BOTH 'a' FROM c1) LTRIM(c1) RTRIM(c1)
1 abc abc abc [abc] bc abc bc abc abc
2 abc abc abc [] NULL NULL NULL NULL NULL
3 abc abc abc [] NULL NULL NULL NULL NULL
4 abc abc abc [ ] NULL NULL
5 abc abc abc [ ] NULL NULL
6 abc abc abc [ a ] a a a a a
7 abc abc abc [aa] NULL NULL NULL aa aa
8 abc abc abc [aabb] bb aabb bb aabb aabb
9 abc abc abc [bbaa] bbaa bb bb bbaa bbaa
10 abc abc abc [aabbaa] bbaa aabb bb aabbaa aabbaa
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
TRIM(TRAILING 'a' FROM c1) AS C2,
TRIM(BOTH 'a' FROM c1) AS C3,
LTRIM(c1) AS C4,
RTRIM(c1) AS C5
FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE "t2" (
"C1" varchar(10) DEFAULT NULL,
"C2" varchar(10) DEFAULT NULL,
"C3" varchar(10) DEFAULT NULL,
"C4" varchar(10) DEFAULT NULL,
"C5" varchar(10) DEFAULT NULL
)
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE trim_oracle (trim_oracle int);
DROP TABLE trim_oracle;
|
