<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Binary String Functions and Operators</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 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="Bit String Functions and Operators" HREF="functions-bitstring.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-string.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions-bitstring.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-BINARYSTRING" >9.5. Binary String Functions and Operators</A ></H1 ><A NAME="AEN8156" ></A ><P > This section describes functions and operators for examining and manipulating values of type <TT CLASS="TYPE" >bytea</TT >. </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" >Table 9-8</A >. Some functions are also implemented using the regular syntax for function invocation. (See <A HREF="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER" >Table 9-9</A >.) </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-BINARYSTRING-SQL" ></A ><P ><B >Table 9-8. <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > Binary String Functions and Operators</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><COL><THEAD ><TR ><TH >Function</TH ><TH >Return Type</TH ><TH >Description</TH ><TH >Example</TH ><TH >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><TT CLASS="PARAMETER" >string</TT > <TT CLASS="LITERAL" >||</TT > <TT CLASS="PARAMETER" >string</TT ></TT ></TD ><TD > <TT CLASS="TYPE" >bytea</TT > </TD ><TD > String concatenation <A NAME="AEN8186" ></A > </TD ><TD ><TT CLASS="LITERAL" >'\\\\Post'::bytea || '\\047gres\\000'::bytea</TT ></TD ><TD ><TT CLASS="LITERAL" >\\Post'gres\000</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >octet_length</CODE >(<TT CLASS="PARAMETER" >string</TT >)</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Number of bytes in binary string</TD ><TD ><TT CLASS="LITERAL" >octet_length( 'jo\\000se'::bytea)</TT ></TD ><TD ><TT CLASS="LITERAL" >5</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >position</CODE >(<TT CLASS="PARAMETER" >substring</TT > in <TT CLASS="PARAMETER" >string</TT >)</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Location of specified substring</TD ><TD ><TT CLASS="LITERAL" >position('\\000om'::bytea in 'Th\\000omas'::bytea)</TT ></TD ><TD ><TT CLASS="LITERAL" >3</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >substring</CODE >(<TT CLASS="PARAMETER" >string</TT > [<SPAN CLASS="OPTIONAL" >from <TT CLASS="TYPE" >integer</TT ></SPAN >] [<SPAN CLASS="OPTIONAL" >for <TT CLASS="TYPE" >integer</TT ></SPAN >])</TT ></TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Extract substring <A NAME="AEN8230" ></A > </TD ><TD ><TT CLASS="LITERAL" >substring('Th\\000omas'::bytea from 2 for 3)</TT ></TD ><TD ><TT CLASS="LITERAL" >h\000o</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >trim</CODE >([<SPAN CLASS="OPTIONAL" >both</SPAN >] <TT CLASS="PARAMETER" >bytes</TT > from <TT CLASS="PARAMETER" >string</TT >)</TT > </TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Remove the longest string containing only the bytes in <TT CLASS="PARAMETER" >bytes</TT > from the start and end of <TT CLASS="PARAMETER" >string</TT > </TD ><TD ><TT CLASS="LITERAL" >trim('\\000'::bytea from '\\000Tom\\000'::bytea)</TT ></TD ><TD ><TT CLASS="LITERAL" >Tom</TT ></TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >get_byte</CODE >(<TT CLASS="PARAMETER" >string</TT >, <TT CLASS="PARAMETER" >offset</TT >)</TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD > Extract byte from string. <A NAME="AEN8260" ></A > </TD ><TD ><TT CLASS="LITERAL" >get_byte('Th\\000omas'::bytea, 4)</TT ></TD ><TD ><TT CLASS="LITERAL" >109</TT ></TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >set_byte</CODE >(<TT CLASS="PARAMETER" >string</TT >, <TT CLASS="PARAMETER" >offset</TT >, <TT CLASS="PARAMETER" >newvalue</TT >)</TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Set byte in string. <A NAME="AEN8275" ></A > </TD ><TD ><TT CLASS="LITERAL" >set_byte('Th\\000omas'::bytea, 4, 64)</TT ></TD ><TD ><TT CLASS="LITERAL" >Th\000o@as</TT ></TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >get_bit</CODE >(<TT CLASS="PARAMETER" >string</TT >, <TT CLASS="PARAMETER" >offset</TT >)</TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD > Extract bit from string. <A NAME="AEN8289" ></A > </TD ><TD ><TT CLASS="LITERAL" >get_bit('Th\\000omas'::bytea, 45)</TT ></TD ><TD ><TT CLASS="LITERAL" >1</TT ></TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >set_bit</CODE >(<TT CLASS="PARAMETER" >string</TT >, <TT CLASS="PARAMETER" >offset</TT >, <TT CLASS="PARAMETER" >newvalue</TT >)</TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Set bit in string. <A NAME="AEN8304" ></A > </TD ><TD ><TT CLASS="LITERAL" >set_bit('Th\\000omas'::bytea, 45, 0)</TT ></TD ><TD ><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 9-9</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" >Table 9-8</A >. </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-BINARYSTRING-OTHER" ></A ><P ><B >Table 9-9. Other Binary String Functions</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><COL><THEAD ><TR ><TH >Function</TH ><TH >Return Type</TH ><TH >Description</TH ><TH >Example</TH ><TH >Result</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >btrim</CODE >(<TT CLASS="PARAMETER" >string</TT > <TT CLASS="TYPE" >bytea</TT >, <TT CLASS="PARAMETER" >bytes</TT > <TT CLASS="TYPE" >bytea</TT >)</TT ></TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Remove the longest string consisting only of bytes in <TT CLASS="PARAMETER" >bytes</TT > from the start and end of <TT CLASS="PARAMETER" >string</TT >. </TD ><TD ><TT CLASS="LITERAL" >btrim('\\000trim\\000'::bytea, '\\000'::bytea)</TT ></TD ><TD ><TT CLASS="LITERAL" >trim</TT ></TD ></TR ><TR ><TD ><TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >length</CODE >(<TT CLASS="PARAMETER" >string</TT >)</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD > Length of binary string <A NAME="AEN8350" ></A > <A NAME="AEN8353" ></A > </TD ><TD ><TT CLASS="LITERAL" >length('jo\\000se'::bytea)</TT ></TD ><TD ><TT CLASS="LITERAL" >5</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >decode</CODE >(<TT CLASS="PARAMETER" >string</TT > <TT CLASS="TYPE" >text</TT >, <TT CLASS="PARAMETER" >type</TT > <TT CLASS="TYPE" >text</TT >)</TT > </TD ><TD ><TT CLASS="TYPE" >bytea</TT ></TD ><TD > Decode binary string from <TT CLASS="PARAMETER" >string</TT > previously encoded with <TT CLASS="LITERAL" >encode</TT >. Parameter type is same as in <TT CLASS="LITERAL" >encode</TT >. </TD ><TD ><TT CLASS="LITERAL" >decode('123\\000456', 'escape')</TT ></TD ><TD ><TT CLASS="LITERAL" >123\000456</TT ></TD ></TR ><TR ><TD > <TT CLASS="LITERAL" ><CODE CLASS="FUNCTION" >encode</CODE >(<TT CLASS="PARAMETER" >string</TT > <TT CLASS="TYPE" >bytea</TT >, <TT CLASS="PARAMETER" >type</TT > <TT CLASS="TYPE" >text</TT >)</TT > </TD ><TD ><TT CLASS="TYPE" >text</TT ></TD ><TD > Encode binary string to <ACRONYM CLASS="ACRONYM" >ASCII</ACRONYM >-only representation. Supported types are: <TT CLASS="LITERAL" >base64</TT >, <TT CLASS="LITERAL" >hex</TT >, <TT CLASS="LITERAL" >escape</TT >. </TD ><TD ><TT CLASS="LITERAL" >encode('123\\000456'::bytea, 'escape')</TT ></TD ><TD ><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-bitstring.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" >Bit String Functions and Operators</TD ></TR ></TABLE ></DIV ></BODY ></HTML >