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
|
# name: test/sql/function/string/test_translate.test
# description: TRANSLATE test
# group: [string]
statement ok
PRAGMA enable_verification
# test translate on NULLs
query T
select TRANSLATE('This is the main test string', NULL, 'ALT')
----
NULL
query T
select TRANSLATE(NULL, 'main', 'ALT')
----
NULL
query T
select TRANSLATE('This is the main test string', 'main', NULL)
----
NULL
# test translate on scalars
query T
select TRANSLATE('12', '2', 'a')
----
1a
query T
select TRANSLATE('abcde', 'abcde', 'fghij')
----
fghij
query T
select TRANSLATE('abcde', 'aabcc', '14235')
----
123de
query T
select TRANSLATE('https://dxyzdb.org', 'zyx.orghttps:/', 'kcu')
----
duckdb
query T
select TRANSLATE('12345', '14367', 'ax')
----
a2x5
query T
select TRANSLATE('hacco worcdxxx', 'acx2', 'el')
----
hello world
query T
select TRANSLATE('hacCo worcd', 'acC', 'ellaabb')
----
hello world
query T
select TRANSLATE('RÄcks', 'Ä', 'A')
----
RAcks
query T
select TRANSLATE('🦆', '🦆', 'D')
----
D
query T
select TRANSLATE('MotörHeadΩ', 'aeΩ', 'eÄ')
----
MotörHÄed
query T
select TRANSLATE('This is 🐱.', '🐱', '🦆')
----
This is 🦆.
query T
select TRANSLATE('Äañt₡Xá你好世界我', 'ñá世界我xyz', 'naDBá')
----
Äant₡Xa你好DBá
# test translate on tables
statement ok
CREATE TABLE strings(a STRING, b STRING)
statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL), ('Hi', '🦆')
query T
select TRANSLATE(a, 'öHl', 'oA-') FROM strings
----
Ae--o
AuL-D
MotorAead
(empty)
Ai
query T
select TRANSLATE(a, 'loD', '🦆') FROM strings
----
He🦆🦆
HuL🦆
MtörHead
(empty)
Hi
query T
select TRANSLATE(b, 'ÄW🦆l', 'ow🐱') FROM strings
----
word
NULL
Rocks
NULL
🐱
query T
select TRANSLATE(a, 'oel', 'OEL') FROM strings WHERE b IS NOT NULL
----
HELLO
MOtörHEad
Hi
# test incorrect usage of translate
statement error
select TRANSLATE(1)
----
<REGEX>:.*Binder Error.*No function matches.*
statement error
select TRANSLATE(1, 2)
----
<REGEX>:.*Binder Error.*No function matches.*
statement error
select TRANSLATE(1, 2, 3, 4)
----
<REGEX>:.*Binder Error.*No function matches.*
|
