<HTML ><HEAD ><TITLE >Binary String Functions and Operators</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.73 "><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.3.2 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="String Functions and Operators" HREF="functions-string.html"><LINK REL="NEXT" TITLE="Pattern Matching" HREF="functions-matching.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META NAME="creation" CONTENT="2003-02-03T20:17:34"></HEAD ><BODY CLASS="SECT1" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#840084" ALINK="#0000FF" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="3" ALIGN="center" >PostgreSQL 7.3.2 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="bottom" ><A HREF="functions-string.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="80%" ALIGN="center" VALIGN="bottom" >Chapter 6. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="bottom" ><A HREF="functions-matching.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-BINARYSTRING" >6.5. Binary String Functions and Operators</A ></H1 ><P > This section describes functions and operators for examining and manipulating binary string values. Strings in this context mean values of the type <TT CLASS="TYPE" >BYTEA</TT >. </P ><P > <SPAN CLASS="ACRONYM" >SQL</SPAN > 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" >Table 6-9</A >. Some functions are also implemented using the regular syntax for function invocation. (See <A HREF="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" >Table 6-10</A >.) </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-BINARYSTRING-SQL" ></A ><P ><B >Table 6-9. <SPAN CLASS="ACRONYM" >SQL</SPAN > Binary String Functions and Operators</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><THEAD ><TR ><TH ALIGN="LEFT" VALIGN="TOP" >Function</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Return Type</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Description</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Example</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD ALIGN="LEFT" VALIGN="TOP" > <TT CLASS="PARAMETER" ><I >string</I ></TT > <TT CLASS="LITERAL" >||</TT > <TT CLASS="PARAMETER" ><I >string</I ></TT > </TD ><TD ALIGN="LEFT" VALIGN="TOP" > <TT CLASS="TYPE" >bytea</TT > </TD ><TD ALIGN="LEFT" VALIGN="TOP" > String concatenation <A NAME="AEN7133" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >'\\\\Post'::bytea || '\\047greSQL\\000'::bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >\\Post'greSQL\000</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >octet_length</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >integer</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" >Number of bytes in binary string</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >octet_length('jo\\000se'::bytea)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >5</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >position</TT >(<TT CLASS="PARAMETER" ><I >substring</I ></TT > in <TT CLASS="PARAMETER" ><I >string</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >integer</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" >Location of specified substring</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >position('\\000om'::bytea in 'Th\\000omas'::bytea)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >3</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >substring</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT > [<SPAN CLASS="OPTIONAL" >from <TT CLASS="TYPE" >integer</TT ></SPAN >] [<SPAN CLASS="OPTIONAL" >for <TT CLASS="TYPE" >integer</TT ></SPAN >])</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Extract substring <A NAME="AEN7174" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >substring('Th\\000omas'::bytea from 2 for 3)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >h\000o</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" > <TT CLASS="FUNCTION" >trim</TT >([<SPAN CLASS="OPTIONAL" >both</SPAN >] <TT CLASS="PARAMETER" ><I >characters</I ></TT > from <TT CLASS="PARAMETER" ><I >string</I ></TT >) </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Remove the longest string containing only the <TT CLASS="PARAMETER" ><I >characters</I ></TT > from the beginning/end/both ends of the <TT CLASS="PARAMETER" ><I >string</I ></TT > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >trim('\\000'::bytea from '\\000Tom\\000'::bytea)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >Tom</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >get_byte</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >, <TT CLASS="PARAMETER" ><I >offset</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >integer</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Extract byte from string. <A NAME="AEN7203" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >get_byte('Th\\000omas'::bytea, 4)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >109</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >set_byte</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >, <TT CLASS="PARAMETER" ><I >offset</I ></TT >, <TT CLASS="PARAMETER" ><I >newvalue</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Set byte in string. <A NAME="AEN7218" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >set_byte('Th\\000omas'::bytea, 4, 64)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >Th\000o@as</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >get_bit</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >, <TT CLASS="PARAMETER" ><I >offset</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >integer</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Extract bit from string. <A NAME="AEN7232" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >get_bit('Th\\000omas'::bytea, 45)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >1</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >set_bit</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >, <TT CLASS="PARAMETER" ><I >offset</I ></TT >, <TT CLASS="PARAMETER" ><I >newvalue</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Set bit in string. <A NAME="AEN7247" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >set_bit('Th\\000omas'::bytea, 45, 0)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >Th\000omAs</TT ></TD ></TR ></TBODY ></TABLE ></DIV ><P > Additional binary string manipulation functions are available and are listed in <A HREF="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" >Table 6-10</A >. Some of them are used internally to implement the <SPAN CLASS="ACRONYM" >SQL</SPAN >-standard string functions listed in <A HREF="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL" >Table 6-9</A >. </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-BINARYSTRING-OTHER" ></A ><P ><B >Table 6-10. Other Binary String Functions</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><THEAD ><TR ><TH ALIGN="LEFT" VALIGN="TOP" >Function</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Return Type</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Description</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Example</TH ><TH ALIGN="LEFT" VALIGN="TOP" >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >btrim</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT > <TT CLASS="TYPE" >bytea</TT > <TT CLASS="PARAMETER" ><I >trim</I ></TT > <TT CLASS="TYPE" >bytea</TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Remove (trim) the longest string consisting only of characters in <TT CLASS="PARAMETER" ><I >trim</I ></TT > from the start and end of <TT CLASS="PARAMETER" ><I >string</I ></TT >. </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >btrim('\\000trim\\000'::bytea,'\\000'::bytea)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >trim</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="FUNCTION" >length</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT >)</TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >integer</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Length of binary string <A NAME="AEN7291" ></A > <A NAME="AEN7294" ></A > </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >length('jo\\000se'::bytea)</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >5</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" > <TT CLASS="FUNCTION" >encode</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT > <TT CLASS="TYPE" >bytea</TT >, <TT CLASS="PARAMETER" ><I >type</I ></TT > <TT CLASS="TYPE" >text</TT >) </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >text</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Encode binary string to <SPAN CLASS="ACRONYM" >ASCII</SPAN >-only representation. Supported types are: base64, hex, escape. </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >encode('123\\000456'::bytea, 'escape')</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >123\000456</TT ></TD ></TR ><TR ><TD ALIGN="LEFT" VALIGN="TOP" > <TT CLASS="FUNCTION" >decode</TT >(<TT CLASS="PARAMETER" ><I >string</I ></TT > <TT CLASS="TYPE" >text</TT >, <TT CLASS="PARAMETER" ><I >type</I ></TT > <TT CLASS="TYPE" >text</TT >) </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="TYPE" >bytea</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" > Decode binary string from <TT CLASS="PARAMETER" ><I >string</I ></TT > previously encoded with <TT CLASS="LITERAL" >encode()</TT >. Parameter type is same as in <TT CLASS="LITERAL" >encode()</TT >. </TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >decode('123\\000456', 'escape')</TT ></TD ><TD ALIGN="LEFT" VALIGN="TOP" ><TT CLASS="LITERAL" >123\000456</TT ></TD ></TR ></TBODY ></TABLE ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions-string.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="functions-matching.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >String Functions and Operators</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Pattern Matching</TD ></TR ></TABLE ></DIV ></BODY ></HTML >