-
Notifications
You must be signed in to change notification settings - Fork 12
/
lib_mysqludf_str.html
394 lines (394 loc) · 13.1 KB
/
lib_mysqludf_str.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
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
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
362
363
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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" type="text/css" href="../mysqludf.css"/>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>lib_mysqludf_str - A library of MySQL UDFs for working with strings</title>
</head>
<body>
<div>
<a href="../index.html">Top</a>
| <a href="../mysql_udf_repository_libraries.html">Up</a>
</div>
<h1>lib_mysqludf_str</h1>
<div>
<a href="lib_mysqludf_str.html">Documentation</a>
| <a href="installdb.sql">Installation</a>
| <a href="lib_mysqludf_str.c">Source</a>
</div>
<p>
MySQL already supports a <a href="http://dev.mysql.com/doc/refman/5.5/en/string-functions.html">number of string functions natively</a>.
However, these string functions are not exhaustive and other string functions can ease the development of MySQL-based applications.
Users coming from a PHP or Perl background, for instance, may expect to find their entire set of string functions in MySQL.
<code>lib_mysqludf_str</code> aims to offer a library of string functions which complement the native ones.
</p>
<p>
The following functions are currently supported in the <code>lib_mysqludf_str</code> library:
</p>
<ol>
<li><a href="#str_numtowords"><code>str_numtowords</code></a> - converts a number to a string.</li>
<li><a href="#str_rot13"><code>str_rot13</code></a> - performs the rot13 transform on a string.</li>
<li><a href="#str_shuffle"><code>str_shuffle</code></a> - randomly shuffles the characters of a string.</li>
<li><a href="#str_translate"><code>str_translate</code></a> - replaces characters contained in srcchar with the corresponding ones in dstchar.</li>
<li><a href="#str_ucfirst"><code>str_ucfirst</code></a> - makes uppercase the first character of the string.</li>
<li><a href="#str_ucwords"><code>str_ucwords</code></a> - transforms to uppercase the first character of each word in a string.</li>
<li><a href="#str_xor"><code>str_xor</code></a> - performs a byte-wise exclusive OR (XOR) of two strings.</li>
<li><a href="#str_srand"><code>str_srand</code></a> - generates a string of cryptographically secure pseudo-random bytes.</li>
</ol>
<p>
Use <a href="#lib_mysqludf_str_info"><code>lib_mysqludf_str_info()</code></a> to obtain information about the currently-installed version of <code>lib_mysqludf_str</code>.
</p>
<a name="str_numtowords"></a><h1>str_numtowords</h1>
<p>
<code>str_numtowords</code> converts numbers written in arabic digits to an english word. All integers in the range <nobr>[-2<sup>63</sup>, 2<sup>63</sup> - 1]</nobr> are supported.
</p>
<h3>Syntax</h3>
<pre>str_numtowords(<b>num</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>num</code></dt>
<dd>
The integer number to be converted to string.
If <code>num</code> is not an integer type or it is NULL, an error will be returned.
</dd>
<dt>returns</dt>
<dd>
The string spelling the given number in English.
</dd>
</dl>
<h3>Examples</h3>
<p>
Converting 123456 to string:
</p>
<pre>
SELECT str_numtowords(123456) as price;
</pre>
<p>yields this result:</p>
<pre>
+----------------------------------------------------------+
| price |
+----------------------------------------------------------+
| one hundred twenty-three thousand four hundred fifty-six |
+----------------------------------------------------------+
</pre>
<a name="str_rot13"></a><h1>str_rot13</h1>
<p>
<code>str_rot13</code> performs the rot13 transform on a string, shifting each character by 13 places in the alphabet, and wrapping back to the beginning if necessary. Non-alphabetic characters are not modified.
</p>
<h3>Syntax</h3>
<pre>str_rot13(<b>subject</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>subject</code></dt>
<dd>
The string to be transformed.
If <code>subject</code> is not a string type or it is NULL, an error will be returned.
</dd>
<dt>returns</dt>
<dd>
The original string with each character shifted by 13 places in the alphabet.
</dd>
</dl>
<h3>Examples</h3>
<p>
Crypting a string using str_rot13:
</p>
<pre>
SELECT str_rot13('secret message') as crypted;
</pre>
<p>yields this result:</p>
<pre>
+----------------+
| crypted |
+----------------+
| frperg zrffntr |
+----------------+
</pre>
<p>
Decrypting a rot13-encoded string (applying again the same function):
</p>
<pre>
SELECT str_rot13('frperg zrffntr') as decrypted;
</pre>
<p>yields this result:</p>
<pre>
+----------------+
| decrypted |
+----------------+
| secret message |
+----------------+
</pre>
<a name="str_shuffle"></a><h1>str_shuffle</h1>
<p>
The <code>str_shuffle</code> function takes a string and randomly shuffles its characters, returning one of its possible permutations.
</p>
<h3>Syntax</h3>
<pre>str_shuffle(<b>subject</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>subject</code></dt>
<dd>
A <code>string</code> value to be shuffled.
If <code>str</code> is not a string type or it is NULL, an error will be returned.
</dd>
<dt>returns</dt>
<dd>
A <code>string</code> value representing one of the possible permutations of the characters composing <code>subject</code>.
</dd>
</dl>
<h3>Examples</h3>
<p>
Shuffling a string:
</p>
<pre>
SELECT str_shuffle('shake me!') as nonsense;
</pre>
<p>yields a result like this:</p>
<pre>
+-----------+
| nonsense |
+-----------+
| esm a!khe |
+-----------+
</pre>
<a name="str_translate"></a><h1>str_translate</h1>
<p>
The <code>str_translate</code> function scans each character in <code>subject</code> string and replaces every occurrence of a character that is contained in <code>srcchar</code> with the corresponding char in <code>dstchar</code>.
</p>
<h3>Syntax</h3>
<pre>str_translate(<b>subject</b>,<b>srcchar</b>, <b>dstchar</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>subject</code></dt>
<dd>
A <code>string</code> value whose characters have to be transformed.
If <code>subject</code> is not a string type or it is NULL, an error will be returned.
</dd>
<dt><code>srcchar</code></dt>
<dd>
A <code>string</code> value containing the characters to be searched and replaced, if present.
If <code>srcchar</code> is not a string type or it is NULL, an error will be returned.
<code>srcchar</code> must contain the same number of characters of <code>dstchar</code>.
</dd>
<dt><code>dstchar</code></dt>
<dd>
A <code>string</code> value containing the characters which will replace the corresponding ones in srcchar.
If <code>dstchar</code> is not a string type or it is NULL, an error will be returned.
<code>dstchar</code> must contain the same number of characters of <code>srcchar</code>.
</dd>
<dt>returns</dt>
<dd>
A <code>string</code> value that is a copy of <code>subject</code> but in which each character present in <code>srcchar</code> is replaced with the corresponding one in <code>dstchar</code>.
</dd>
</dl>
<h3>Examples</h3>
<p>
Replacing 'a' with 'x' and 'b' with 'y':
</p>
<pre>
SELECT str_translate('a big string', 'ab', 'xy');
</pre>
<p>yields this result:</p>
<pre>
+--------------+
| translated |
+--------------+
| x yig string |
+--------------+
</pre>
<a name="str_ucfirst"></a><h1>str_ucfirst</h1>
<p>
The <code>str_ucfirst</code> function is the MySQL equivalent for PHP's <code>ucfirst()</code>.
It takes a string and transforms its first characters into uppercase.
</p>
<h3>Syntax</h3>
<pre>str_ucfirst(<b>subject</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>subject</code></dt>
<dd>
A <code>string</code> value whose first character will be transformed into uppercase.
If <code>subject</code> is not a string type or it is NULL, an error will be returned.
</dd>
<dt>returns</dt>
<dd>
A <code>string</code> value with the first character of <code>subject</code> capitalized, if that character is alphabetic.
</dd>
</dl>
<h3>Examples</h3>
<p>
Capitalizing a string:
</p>
<pre>
SELECT str_ucfirst('sample string') as capitalized;
</pre>
<p>yields this result:</p>
<pre>
+---------------+
| capitalized |
+---------------+
| Sample string |
+---------------+
</pre>
<h3>See Also</h3>
<ul>
<li><a href="#str_ucwords"><code>str_ucwords</code></a></li>
</ul>
<a name="str_ucwords"></a><h1>str_ucwords</h1>
<p>
The <code>str_ucwords</code> function is the MySQL equivalent for PHP's <code>ucwords()</code>.
It takes a string and transforms the first character of each of its word into uppercase.
</p>
<h3>Syntax</h3>
<pre>str_ucwords(<b>subject</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>subject</code></dt>
<dd>
A <code>string</code> value where the first character of each string will be transformed into uppercase.
If <code>subject</code> is not a string type or it is NULL, an error will be returned.
</dd>
<dt>returns</dt>
<dd>
A <code>string</code> value with the first character of each word in <code>subject</code> capitalized, if that character is alphabetic.
</dd>
</dl>
<h3>Examples</h3>
<p>
Capitalizing the first character of each word in a string:
</p>
<pre>
SELECT str_ucwords('a string composed of many words') as capitalized;
</pre>
<p>yields this result:</p>
<pre>
+---------------------------------+
| capitalized |
+---------------------------------+
| A String Composed Of Many Words |
+---------------------------------+
</pre>
<h3>See Also</h3>
<ul>
<li><a href="#str_ucfirst"><code>str_ucfirst</code></a></li>
</ul>
<a name="str_xor"></a><h1>str_xor</h1>
<p>
The <code>str_xor</code> function performs a byte-wise exclusive OR (XOR) of two strings.
</p>
<h3>Syntax</h3>
<pre>str_xor(<b>string1</b>, <b>string2</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>string1</code></dt>
<dd>
The first string. If <code>string1</code> is not a string or is NULL, then an error is returned.
</dd>
<dt><code>string2</code></dt>
<dd>
The second string. If <code>string2</code> is not a string or is NULL, then an error is returned.
</dd>
<dt>returns</dt>
<dd>
The <code>string</code> value that is obtained by XORing each byte of <code>string1</code> with the corresponding byte of <code>string2</code>.
</dd>
</dl>
<p>
Note that if <code>string1</code> or <code>string2</code> is longer than the other, then the shorter
string is considered to be padded with enough trailing NUL bytes (0x00) for the two strings to have the
same length.
</p>
<h3>Examples</h3>
<pre>
SELECT HEX(str_xor(UNHEX('0E33'), UNHEX('E0'))) as result;
</pre>
<p>yields this result:</p>
<pre>
+--------+
| result |
+--------+
| EE33 |
+--------+
</pre>
<pre>
SELECT HEX(str_xor('Wiki', UNHEX('F3F3F3F3'))) as result;
</pre>
<p>yields this result:</p>
<pre>
+----------+
| result |
+----------+
| A49A989A |
+----------+
</pre>
<h3>Since</h3>
<p>Version 0.2</p>
<h3>See Also</h3>
<ul>
<li><q><a href="https://secure.wikimedia.org/wikipedia/en/wiki/XOR_cipher">XOR cipher</a></q>. Wikipedia.</li>
</ul>
<a name="str_srand"></a><h1>str_srand</h1>
<p>
The <code>str_srand</code> function generates a string of random bytes from a cryptographically secure pseudo random number generator (CSPRNG).
</p>
<h3>Syntax</h3>
<pre>str_srand(<b>length</b>)</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt><code>length</code></dt>
<dd>
<p>The number of pseudo-random bytes to generate, and the length of the string. If <code>length</code> is not a non-negative integer or is NULL, then an error is returned.</p>
<p><b>Note:</b> To prevent Denial of Service, <code>length</code> is limited to the compile-time constant <code>MAX_RANDOM_BYTES</code>. By default, <code>MAX_RANDOM_BYTES</code> is 4096 (4 KiB).</p>
</dd>
<dt>returns</dt>
<dd>
A <code>string</code> value comprised of <code>length</code> cryptographically secure pseudo-random bytes.
</dd>
</dl>
<h3>Examples</h3>
<pre>
SELECT str_srand(5) as result;
</pre>
<p>yields a random string containing 5 bytes.</p>
<pre>
mysql> SELECT LENGTH(str_srand(5)) as len;
+-----+
| len |
+-----+
| 5 |
+-----+
</pre>
<h3>Since</h3>
<p>Version 0.3</p>
<h3>See Also</h3>
<ul>
<li><q><a href="https://secure.wikimedia.org/wikipedia/en/wiki/CSPRNG">CSPRNG</a></q>. Wikipedia.</li>
</ul>
<a name="lib_mysqludf_str_info"></a><h1>lib_mysqludf_str_info</h1>
<p>
The <code>lib_mysqludf_str_info</code> function returns information about the currently-installed version of <code>lib_mysqludf_str</code>.
</p>
<h3>Syntax</h3>
<pre>lib_mysqludf_str_info()</pre>
<h3>Parameters and Return Values</h3>
<dl>
<dt>returns</dt>
<dd>
A <code>string</code> value containing the version of <code>lib_mysqludf_str</code> that is installed.
</dd>
</dl>
<h3>Examples</h3>
<pre>
SELECT lib_mysqludf_str_info() as info;
</pre>
<p>yields this result:</p>
<pre>
+------------------------------+
| info |
+------------------------------+
| lib_mysqludf_str version 0.3 |
+------------------------------+
</pre>
</body>
</html>