-
Notifications
You must be signed in to change notification settings - Fork 39
/
functions.html
310 lines (290 loc) · 20.6 KB
/
functions.html
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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
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
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
<!DOCTYPE html>
<html lang="en">
<head>
<title>Sqlime / Functions</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta property="og:site_name" content="Sqlime" />
<meta property="og:title" content="Sqlime - Online SQLite playground" />
<meta name="description" content="Sqlime is an online SQLite playground for debugging and sharing SQL snippets.">
<meta property="og:description"
content="Sqlime is an online SQLite playground for debugging and sharing SQL snippets." />
<meta property="og:url" content="https://sqlime.org/functions.html" />
<meta property="og:image" content="https://sqlime.org/img/cover.png" />
<meta name="twitter:card" content="summary_large_image" />
<meta name="theme-color" content="#ffffff">
<link rel="icon" href="/img/favicon.ico" sizes="any">
<link rel="icon" type="image/svg+xml" href="/img/favicon.svg">
<link rel="apple-touch-icon" href="/img/apple-touch-icon.png">
<link rel="manifest" href="/site.webmanifest">
<link rel="stylesheet" href="css/awsm.css">
<link rel="stylesheet" href="css/article.css">
</head>
<body>
<header>
<h3>
<a class="logo" href="/">
<img src="/img/favicon-64x64.png">
sqlime
</a>
</h3>
<p>an online SQL playground</p>
</header>
<main>
<article>
<h1>Available SQL Functions</h1>
<p>
Sqlime offers both standard SQLite functions and many more from the
<a href="https://github.com/nalgeon/sqlean">Sqlean</a> project:
</p>
<p class="columns-3">
<a href="#general">general-purpose</a><br>
<a href="#date">date and time</a><br>
<a href="#math">math</a><br>
<a href="#aggregate">aggregate</a><br>
<a href="#window">window</a><br>
<a href="#crypto">encode/decode</a><br>
<a href="#define">dynamic SQL</a><br>
<a href="#fuzzy">fuzzy matching</a><br>
<a href="#ipaddr">IP addresses</a><br>
<a href="#regexp">regular expressions</a><br>
<a href="#stats">statistics</a><br>
<a href="#text">text</a><br>
<a href="#uuid">UUIDs</a><br>
</p>
<h5 id="general">General-purpose functions</h5>
<p class="columns-3">
<a href="https://sqlite.org/lang_corefunc.html#abs">abs</a><br>
<a href="https://sqlite.org/lang_corefunc.html#changes">changes</a><br>
<a href="https://sqlite.org/lang_corefunc.html#char">char</a><br>
<a href="https://sqlite.org/lang_corefunc.html#coalesce">coalesce</a><br>
<a href="https://sqlite.org/lang_corefunc.html#format">format</a><br>
<a href="https://sqlite.org/lang_corefunc.html#glob">glob</a><br>
<a href="https://sqlite.org/lang_corefunc.html#hex">hex</a><br>
<a href="https://sqlite.org/lang_corefunc.html#ifnull">ifnull</a><br>
<a href="https://sqlite.org/lang_corefunc.html#iif">iif</a><br>
<a href="https://sqlite.org/lang_corefunc.html#instr">instr</a><br>
<a href="https://sqlite.org/lang_corefunc.html#last_insert_rowid">last_insert_rowid</a><br>
<a href="https://sqlite.org/lang_corefunc.html#length">length</a><br>
<a href="https://sqlite.org/lang_corefunc.html#like">like</a><br>
<a href="https://sqlite.org/lang_corefunc.html#lower">lower</a><br>
<a href="https://sqlite.org/lang_corefunc.html#ltrim">ltrim</a><br>
<a href="https://sqlite.org/lang_corefunc.html#max">max</a><br>
<a href="https://sqlite.org/lang_corefunc.html#min">min</a><br>
<a href="https://sqlite.org/lang_corefunc.html#nullif">nullif</a><br>
<a href="https://sqlite.org/lang_corefunc.html#quote">quote</a><br>
<a href="https://sqlite.org/lang_corefunc.html#random">random</a><br>
<a href="https://sqlite.org/lang_corefunc.html#randomblob">randomblob</a><br>
<a href="https://sqlite.org/lang_corefunc.html#replace">replace</a><br>
<a href="https://sqlite.org/lang_corefunc.html#round">round</a><br>
<a href="https://sqlite.org/lang_corefunc.html#rtrim">rtrim</a><br>
<a href="https://sqlite.org/lang_corefunc.html#sign">sign</a><br>
<a href="https://sqlite.org/lang_corefunc.html#soundex">soundex</a><br>
<a href="https://sqlite.org/lang_corefunc.html#sqlite_version">sqlite_version</a><br>
<a href="https://sqlite.org/lang_corefunc.html#substr">substring</a><br>
<a href="https://sqlite.org/lang_corefunc.html#total_changes">total_changes</a><br>
<a href="https://sqlite.org/lang_corefunc.html#trim">trim</a><br>
<a href="https://sqlite.org/lang_corefunc.html#typeof">typeof</a><br>
<a href="https://sqlite.org/lang_corefunc.html#unhex">unhex</a><br>
<a href="https://sqlite.org/lang_corefunc.html#unicode">unicode</a><br>
<a href="https://sqlite.org/lang_corefunc.html#upper">upper</a><br>
<a href="https://sqlite.org/lang_corefunc.html#zeroblob">zeroblob</a><br>
</p>
<h5 id="date">Date and time functions</h5>
<p class="columns-3">
<a href="https://sqlite.org/lang_datefunc.html">date</a><br>
<a href="https://sqlite.org/lang_datefunc.html">time</a><br>
<a href="https://sqlite.org/lang_datefunc.html">datetime</a><br>
<a href="https://sqlite.org/lang_datefunc.html">julianday</a><br>
<a href="https://sqlite.org/lang_datefunc.html">unixepoch</a><br>
<a href="https://sqlite.org/lang_datefunc.html">strftime</a><br>
</p>
<h5 id="math">Math functions</h5>
<p class="columns-3">
<a href="https://sqlite.org/lang_mathfunc.html#acos">acos</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#acosh">acosh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#asin">asin</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#asinh">asinh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#atan">atan</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#atan2">atan2</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#atanh">atanh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#ceil">ceil</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#cos">cos</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#cosh">cosh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#degrees">degrees</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#exp">exp</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#floor">floor</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#ln">ln</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#log">log</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#log10">log10</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#log2">log2</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#mod">mod</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#pi">pi</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#pow">pow</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#radians">radians</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#sin">sin</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#sinh">sinh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#sqrt">sqrt</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#tan">tan</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#tanh">tanh</a><br>
<a href="https://sqlite.org/lang_mathfunc.html#trunc">trunc</a><br>
</p>
<h5 id="aggregate">Aggregate functions</h5>
<p class="columns-3">
<a href="https://sqlite.org/lang_aggfunc.html#avg">avg</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#count">count</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#group_concat">group_concat</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#max_agg">max</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#min_agg">min</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#sumunc">sum</a><br>
<a href="https://sqlite.org/lang_aggfunc.html#sumunc">total</a><br>
</p>
<h5 id="window">Window functions</h5>
<p class="columns-3">
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">avg</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">count</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">cume_dist</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">dense_rank</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">first_value</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">group_concat</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">lag</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">last_value</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">lead</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">max</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">min</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">nth_value</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">ntile</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">percent_rank</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">rank</a><br>
<a href="https://sqlite.org/windowfunctions.html#built_in_window_functions">row_number</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">sum</a><br>
<a href="https://sqlite.org/windowfunctions.html#aggregate_window_functions">total</a><br>
</p>
<h5 id="crypto">Encode/decode functions</h5>
<p class="columns-3">
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#encoding-and-decoding-functions">decode</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#encoding-and-decoding-functions">encode</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#hashing-and-message-digest-functions">md5</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#hashing-and-message-digest-functions">sha1</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#hashing-and-message-digest-functions">sha256</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#hashing-and-message-digest-functions">sha384</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/crypto.md#hashing-and-message-digest-functions">sha512</a><br>
</p>
<h5 id="define">Dynamic SQL</h5>
<p>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/define.md#scalar-functions">define</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/define.md#arbitrary-sql-statements">eval</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/define.md#scalar-functions">undefine</a>
</p>
<h5 id="fuzzy">Fuzzy string matching</h5>
<p class="columns-3">
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#phonetic-codes">caverphone</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#phonetic-codes">phonetic_hash</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#phonetic-codes">rsoundex</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#phonetic-codes">soundex</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">dlevenshtein</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">edit_distance</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">hamming</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">jaro_winkler</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">levenshtein</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#string-distances">osa_distance</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md#transliteration">translit</a><br>
</p>
<h5 id="ipaddr">IP address manipulation</h5>
<p class="columns-3">
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/ipaddr.md#ipcontains">ipcontains</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/ipaddr.md#ipfamily">ipfamilyip</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/ipaddr.md#iphost">iphost</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/ipaddr.md#ipmasklen">ipmasklen</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/ipaddr.md#ipnetwork">ipnetwork</a><br>
</p>
<h5 id="regexp">Regular expressions</h5>
<p class="columns-3">
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md#regexp_capture">regexp_capture</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md#regexp_like">regexp_like</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md#regexp_replace">regexp_replace</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md#regexp_substr">regexp_substr</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md#regexp-statement">regexp</a><br>
</p>
<h5 id="stats">Mathematical Statistics</h5>
<p class="columns-3">
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#generate_series">generate_series</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">median</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile_25</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile_75</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile_90</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile_95</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile_99</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">percentile</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">stddev_pop</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">stddev</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">var_pop</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/stats.md#aggregate-functions">variance</a><br>
</p>
<h5 id="text">Text functions</h5>
<p class="columns-3">
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_bitsize">text_bitsize</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_concat">text_concat</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_contains">text_contains</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_count">text_count</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_has_prefix">text_has_prefix</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_has_suffix">text_has_suffix</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_index">text_index</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_join">text_join</a><br>
<a
href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_last_index">text_last_index</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_left">text_left</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_length">text_length</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_lpad">text_lpad</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_ltrim">text_ltrim</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_repeat">text_repeat</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_replace">text_replace</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_reverse">text_reverse</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_right">text_right</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_rpad">text_rpad</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_rtrim">text_rtrim</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_size">text_size</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_slice">text_slice</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_split">text_split</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_substring">text_substring</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_translate">text_translate</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/text.md#text_trim">text_trim</a><br>
</p>
<h5 id="uuid">Universally Unique IDentifiers</h5>
<p class="columns-3">
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/uuid.md#uuid_blob">uuid_blob</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/uuid.md#uuid_str">uuid_str</a><br>
<a href="https://github.com/nalgeon/sqlean/blob/main/docs/uuid.md#uuid4">uuid4</a><br>
</p>
<p><a href="/">← back</a></p>
</article>
</main>
<footer>
SQL for the win!
</footer>
</body>
</html>