File: functions-binarystring.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (218 lines) | stat: -rw-r--r-- 10,387 bytes parent folder | download 
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
 
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.5.Binary String Functions and Operators</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions-string.html" title="9.4.String Functions and Operators">
<link rel="next" href="functions-bitstring.html" title="9.6.Bit String Functions and Operators">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-binarystring"></a>9.5.Binary String Functions and Operators</h2></div></div></div>
<a name="id601956"></a><p>    This section describes functions and operators for examining and
    manipulating values of type <code class="type">bytea</code>.
   </p>
<p>    <acronym class="acronym">SQL</acronym> defines some string functions with a
    special syntax where 
    certain key words rather than commas are used to separate the
    arguments.  Details are in
    <a href="functions-binarystring.html#functions-binarystring-sql" title="Table9.8.SQL Binary String Functions and Operators">Table9.8, &#8220;<acronym class="acronym">SQL</acronym> Binary String Functions and Operators&#8221;</a>.
    Some functions are also implemented using the regular syntax for
    function invocation.
    (See <a href="functions-binarystring.html#functions-binarystring-other" title="Table9.9.Other Binary String Functions">Table9.9, &#8220;Other Binary String Functions&#8221;</a>.)
   </p>
<div class="table">
<a name="functions-binarystring-sql"></a><p class="title"><b>Table9.8.<acronym class="acronym">SQL</acronym> Binary String Functions and Operators</b></p>
<div class="table-contents"><table summary="SQL Binary String Functions and Operators" border="1">
<colgroup>
<col>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Function</th>
<th>Return Type</th>
<th>Description</th>
<th>Example</th>
<th>Result</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><em class="parameter"><code>string</code></em> <code class="literal">||</code>
        <em class="parameter"><code>string</code></em></code></td>
<td> <code class="type">bytea</code> </td>
<td>        String concatenation
        <a name="id602070"></a>
       </td>
<td><code class="literal">'\\\\Post'::bytea || '\\047gres\\000'::bytea</code></td>
<td><code class="literal">\\Post'gres\000</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">octet_length</code>(<em class="parameter"><code>string</code></em>)</code></td>
<td><code class="type">int</code></td>
<td>Number of bytes in binary string</td>
<td><code class="literal">octet_length( 'jo\\000se'::bytea)</code></td>
<td><code class="literal">5</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">position</code>(<em class="parameter"><code>substring</code></em> in <em class="parameter"><code>string</code></em>)</code></td>
<td><code class="type">int</code></td>
<td>Location of specified substring</td>
<td><code class="literal">position('\\000om'::bytea in 'Th\\000omas'::bytea)</code></td>
<td><code class="literal">3</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">substring</code>(<em class="parameter"><code>string</code></em> [<span class="optional">from <code class="type">int</code></span>] [<span class="optional">for <code class="type">int</code></span>])</code></td>
<td><code class="type">bytea</code></td>
<td>        Extract substring
        <a name="id602215"></a>
       </td>
<td><code class="literal">substring('Th\\000omas'::bytea from 2 for 3)</code></td>
<td><code class="literal">h\000o</code></td>
</tr>
<tr>
<td>        <code class="literal"><code class="function">trim</code>([<span class="optional">both</span>]
        <em class="parameter"><code>bytes</code></em> from
        <em class="parameter"><code>string</code></em>)</code>
       </td>
<td><code class="type">bytea</code></td>
<td>        Remove the longest string containing only the bytes in
        <em class="parameter"><code>bytes</code></em> from the start
        and end of <em class="parameter"><code>string</code></em>
       </td>
<td><code class="literal">trim('\\000'::bytea from '\\000Tom\\000'::bytea)</code></td>
<td><code class="literal">Tom</code></td>
</tr>
<tr>
<td>
<code class="function">get_byte</code>(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>)</td>
<td><code class="type">int</code></td>
<td>        Extract byte from string
        <a name="id602329"></a>
       </td>
<td><code class="literal">get_byte('Th\\000omas'::bytea, 4)</code></td>
<td><code class="literal">109</code></td>
</tr>
<tr>
<td>
<code class="function">set_byte</code>(<em class="parameter"><code>string</code></em>,
       <em class="parameter"><code>offset</code></em>, <em class="parameter"><code>newvalue</code></em>)</td>
<td><code class="type">bytea</code></td>
<td>        Set byte in string
        <a name="id602385"></a>
       </td>
<td><code class="literal">set_byte('Th\\000omas'::bytea, 4, 64)</code></td>
<td><code class="literal">Th\000o@as</code></td>
</tr>
<tr>
<td>
<code class="function">get_bit</code>(<em class="parameter"><code>string</code></em>, <em class="parameter"><code>offset</code></em>)</td>
<td><code class="type">int</code></td>
<td>        Extract bit from string
        <a name="id602434"></a>
       </td>
<td><code class="literal">get_bit('Th\\000omas'::bytea, 45)</code></td>
<td><code class="literal">1</code></td>
</tr>
<tr>
<td>
<code class="function">set_bit</code>(<em class="parameter"><code>string</code></em>,
       <em class="parameter"><code>offset</code></em>, <em class="parameter"><code>newvalue</code></em>)</td>
<td><code class="type">bytea</code></td>
<td>        Set bit in string
        <a name="id602489"></a>
       </td>
<td><code class="literal">set_bit('Th\\000omas'::bytea, 45, 0)</code></td>
<td><code class="literal">Th\000omAs</code></td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p>    Additional binary string manipulation functions are available and
    are listed in <a href="functions-binarystring.html#functions-binarystring-other" title="Table9.9.Other Binary String Functions">Table9.9, &#8220;Other Binary String Functions&#8221;</a>.  Some
    of them are used internally to implement the
    <acronym class="acronym">SQL</acronym>-standard string functions listed in <a href="functions-binarystring.html#functions-binarystring-sql" title="Table9.8.SQL Binary String Functions and Operators">Table9.8, &#8220;<acronym class="acronym">SQL</acronym> Binary String Functions and Operators&#8221;</a>.
   </p>
<div class="table">
<a name="functions-binarystring-other"></a><p class="title"><b>Table9.9.Other Binary String Functions</b></p>
<div class="table-contents"><table summary="Other Binary String Functions" border="1">
<colgroup>
<col>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Function</th>
<th>Return Type</th>
<th>Description</th>
<th>Example</th>
<th>Result</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><code class="function">btrim</code>(<em class="parameter"><code>string</code></em>
        <code class="type">bytea</code>, <em class="parameter"><code>bytes</code></em> <code class="type">bytea</code>)</code></td>
<td><code class="type">bytea</code></td>
<td>        Remove the longest string consisting only of bytes
        in <em class="parameter"><code>bytes</code></em> from the start and end of
        <em class="parameter"><code>string</code></em>
      </td>
<td><code class="literal">btrim('\\000trim\\000'::bytea, '\\000'::bytea)</code></td>
<td><code class="literal">trim</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">length</code>(<em class="parameter"><code>string</code></em>)</code></td>
<td><code class="type">int</code></td>
<td>       Length of binary string
       <a name="id602654"></a>
       <a name="id602665"></a>
      </td>
<td><code class="literal">length('jo\\000se'::bytea)</code></td>
<td><code class="literal">5</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">md5</code>(<em class="parameter"><code>string</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>       Calculates the MD5 hash of <em class="parameter"><code>string</code></em>,
       returning the result in hexadecimal
      </td>
<td><code class="literal">md5('Th\\000omas'::bytea)</code></td>
<td><code class="literal">8ab2d3c9689aaf18 b4958c334c82d8b1</code></td>
</tr>
<tr>
<td>       <code class="literal"><code class="function">decode</code>(<em class="parameter"><code>string</code></em> <code class="type">text</code>,
              <em class="parameter"><code>type</code></em> <code class="type">text</code>)</code>
      </td>
<td><code class="type">bytea</code></td>
<td>       Decode binary string from <em class="parameter"><code>string</code></em> previously 
       encoded with <code class="literal">encode</code>.  Parameter type is same as in <code class="literal">encode</code>.
      </td>
<td><code class="literal">decode('123\\000456', 'escape')</code></td>
<td><code class="literal">123\000456</code></td>
</tr>
<tr>
<td>       <code class="literal"><code class="function">encode</code>(<em class="parameter"><code>string</code></em> <code class="type">bytea</code>,
              <em class="parameter"><code>type</code></em> <code class="type">text</code>)</code>
      </td>
<td><code class="type">text</code></td>
<td>       Encode binary string to <acronym class="acronym">ASCII</acronym>-only representation.  Supported
       types are: <code class="literal">base64</code>, <code class="literal">hex</code>, <code class="literal">escape</code>.
      </td>
<td><code class="literal">encode('123\\000456'::bytea, 'escape')</code></td>
<td><code class="literal">123\000456</code></td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break">
</div></body>
</html>