Chapter 5 – Binary strings¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

For more information specific to CrateDB, check out the CrateDB Reference.

In SQL, a binary string, (or BLOB), is any arbitrary sequence of zero or more octets that isn’t associated with either a Character set or a Collation. A BLOB value may be a <literal>, the value of a parameter or a host language variable or the result of any expression or argument (including a possibly qualified <Column name>) that evaluates to a binary string. BLOBs represent an unknown lump of binary data: the typical use of a BLOB <data type> is to store an image.

Octets in a BLOB are numbered from left to right beginning with 1 (the most significant octet). BLOBs are stored in the binary string <data type>: BLOB.

Table of Contents

<BLOB literal>s ¶

A <BLOB literal> is the letter “X” (upper case mandatory) followed by a string of zero or more hexits inside a pair of single quote marks. A hexit is either (a) any of the digits 0 through 9 or (b) any of the letters A through F (upper case or lower case allowed) and is four bits long – 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E and F are interpreted as 0000, 0001, 0010, 0011, 0100, 0101, 0110, 0111, 1000, 1001, 1010, 1011, 1100, 1101, 1110 and 1111, respectively.) Its <data type> is variable length BLOB. The <literal>’s length is four times the number of hexits inside the quote marks; the delimiting single quotes are not part of the <literal>, therefore they are not included in the calculation of the <BLOB literal>’s size. Here are some examples of <BLOB literal>s:

X'49FE'
X'a31d'

[Obscure Rule] A long <BLOB literal> may be broken up into two or more smaller <BLOB literal>s, split by a <separator> that must include a newline character. When such a <literal> is encountered, your DBMS will ignore the <separator> and treat the multiple strings as a single <literal>. For example, these two <BLOB literal>s are equivalent:

X'49FE'
'A31D'
X'49FEA31D'

(In the first example, there is a carriage return newline <separator> between FE' and 'A3.)

BLOB <data type>s ¶

A BLOB <data type> is defined by a descriptor that contains two pieces of information:

The <data type>’s name: BINARY LARGE OBJECT.
The <data type>’s maximum length in octets.

BLOB ¶

The required syntax for a BINARY LARGE OBJECT <data type> specification is as follows.

BINARY LARGE OBJECT <data type> ::=
{ BINARY LARGE OBJECT | BLOB } [ (length) ]

BINARY LARGE OBJECT may be abbreviated as BLOB and is a variable length binary string, up to “length” octets long; it defines a set of binary string values that are any correctly sized string of octets that are not associated with a Character set or a Collation. For example, these <BLOB literal>s:

X'49FE'
X'178FA3A8'

are both valid values for this <data type> specification:

BLOB(8)

The optional length, if specified, is an unsigned positive integer, possibly followed by a letter code (either “K”, “M” or “G”), that defines the maximum octet length of acceptable values in the BLOB field.

If the length n is not followed by a letter code, the BLOB may hold up to n ocets.
The length may include the letter code “K” (kilobyte), “M” (megabyte), or “G” (gigabyte). If the length is defined as nK, the BLOB may hold up to n*1,024 octets. If the length is defined as nM, the BLOB may hold up to integer*1,048,576 octets. If the length is defined as nG, the BLOB may hold up to n*1,073,741,824 octets.

For example, this specification defines a set of binary string values that may range from zero to 20 ocets:

BLOB(20)

(Zero length binary strings can be stored in a BLOB field.)

This <data type> specification defines a set of binary string values that may range from zero to 2048 octets:

BLOB(2K)

This <data type> specification defines a set of binary string values that may range from zero to 2,097,152 octets:

BLOB(2M)

And this <data type> specification defines a set of binary string values that may range from zero to 2,147,483,648 octets:

BLOB(2G)

[NON-PORTABLE] The default length and the maximum length for BLOB are non-standard because the SQL Standard requires implementors to define BLOB’s default and maximum lengths. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the length of BLOB to range from 1 to 32 and sets the default length of a BLOB <data type> to 1K. For example, these two <data type> specifications are equivalent: both define a set of binary string values that may range from zero to 1024 octets:

BLOB
BLOB(1K)

Now that we’ve described SQL’s BLOB <data type>, let’s look at some example SQL statements that put it to use.

These SQL statements make a Table with one binary string Column, insert a row, then search for any binary string equal to the bit string 01000100.

CREATE TABLE Binary_Examples (
      occurrence_binary BLOB(2K));

INSERT INTO Binary_Examples (
      occurrence_binary)
      VALUES (X'4D');

SELECT occurrence_binary,
FROM   Binary_Examples
WHERE  occurrence_binary = X'44';

BLOB Operations ¶

A BLOB is compatible with, and comparable to, all other BLOBs – that is, all BLOBs are mutually assignable and mutually comparable. BLOBs may not be directly compared with, or directly assigned to, any other <data type> class, though implicit type conversions can sometimes occur in expressions, SELECTs, INSERTs, DELETEs and UPDATEs. Explicit BLOB conversions can be forced with the CAST operator.

CAST¶

In SQL, CAST is a scalar operator that converts a given scalar value to a given scalar <data type>. The required syntax for the CAST operator is:

CAST (<cast operand> AS <cast target>)
   <cast operand> ::= scalar_expression
   <cast target> ::= <Domain name> | <data type>

The CAST operator converts values of a source <data type> into values of a target <data type>, where each <data type> is an SQL pre-defined <data type> (data conversions between UDTs are done with a user-defined cast). The source <data type> (or <cast operand>) can be any expression that evaluates to a single value. The target <data type> (or <cast target>) is either an SQL predefined <data type> specification or the name of a Domain whose defined <data type> is your target <data type>. If you use CAST (... AS <Domain name>), your current <AuthorizationID> must have the USAGE Privilege on that Domain.

It isn’t, of course, possible to convert the values of every <data type> into the values of every other <data type>. For BLOBs, the rules are:

CAST (NULL AS <data type>) and CAST (blob_source_is_a_null_value AS <data type>) both result in NULL.
You can CAST a BLOB source to a BLOB target. You can also CAST a BLOB source to a UDT target or a <reference type> target if a user-defined cast exists for this purpose and your current <AuthorizationID> has the EXECUTE Privilege on that user-defined cast.

When you CAST a BLOB to a BLOB target, if the octet length of the source value is less than or equals the maximum octet length of the target, the result of the CAST is the source BLOB value. If the octet length of the source value is greater than the maximum octet length of the target, the result of the CAST is as much of the source BLOB value as will fit into the target – in this case, and your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

When you CAST a BLOB to a UDT or a <reference type> target, your DBMS invokes the user defined cast routine, with the source value as the routine’s argument. The CAST result is the value returned by the user defined cast.

If you want to restrict your code to Core SQL, (a) don’t use <Domain name> as a CAST target: CAST only to a <data type> and (b) don’t use CAST to convert any BLOB value to another <data type>.

Assignment¶

In SQL, when a BLOB is assigned to a BLOB target, the assignment is done one octet at a time, from left to right – that is, the source value’s most significant octet is assigned to the target’s most significant octet, then the source’s next octet is assigned to the target’s next octet, and so on.

When a BLOB is taken from SQL-data to be assigned to a BLOB target, the size of the target is first set either to the size of the source or to its own maximum length, whichever is less. If the source is longer than the target, the source is truncated to fit the target. In this case, your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

Here are some examples of the result you´ll get when you move a BLOB from your database to a host language program (assume your host language variable has a variable size of 2 ocets).

source X'5550' yields a target value of 5550
source X'55' yields a target value of 55
source X'555500' yields a target value of 5555 and SQLSTATE 01004
source X'555555' yields a target value of 5555 and SQLSTATE 01004

[Obscure Rule] Since only SQL accepts null values, if your source is NULL, then your target’s value is not changed. Instead, your DBMS will set its indicator parameter to -1, to indicate that an assignment of the null value was attempted. If your target doesn’t have an indicator parameter, the assignment will fail: your DBMS will return the SQLSTATE error 22002 "data exception-null value, no indicator parameter". If your source is a non-null value that fits into your target, your DBMS will set the target’s indicator parameter (if any) to zero. If your source is longer than your target, your DBMS will set your target’s indicator parameter to the length of the source; that is, if your source is 12 octets long and your target can accept only 10 octets, your DBMS will set the target’s indicator parameter to 12, to indicate that 2 octets were lost on assignment. If the source’s length is too big to be assigned to the indicator, the assignment will fail: your DBMS will return the SQLSTATE error 22022 "data exception-indicator overflow". We’ll talk more about indicator parameters in our chapters on SQL binding styles.

When a binary string is assigned to a SQL-data BLOB target, the size of the target is first set either to the size of the source or to its own maximum length, whichever is less. If the source is larger than the target, but the extra octets are all 0-octets, the source’s significant octet value is assigned to the target. If the source is larger than the target and the extra octets are not all 0-octets, the assignment will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation".

Here are some examples of the result you´ll get when you assign a binary string to your SQL database (assume your target is defined as BLOB (2)):

source X'5550' yields a target value of 5550
source X'55' yields a target value of 55
source X'555500' yields a target of 5555
source X'555555' yields no change to target; assinment fails
with SQLSTATE 22001

[Obscure Rule] There are two ways to assign a null value to an SQL-data target. Within SQL, you can use the <keyword> NULL in an INSERT or an UPDATE statement to indicate that the target should be set to NULL; that is, if your source is NULL, your DBMS will set your target to NULL. Outside of SQL, if your source has an indicator parameter that is set to -1, your DBMS will set your target to NULL (regardless of the value of the source). An indicator parameter with a value less than -1 will cause an error: your DBMS will return the SQLSTATE error 22010 "data exception-invalid indicator parameter value".

Comparison ¶

SQL provides only two scalar comparison operators = and <>, to perform operations on BLOBs. These will be familiar; there are equivalent operators in other computer languages. If any of the comparands are NULL, the result of the operation is UNKNOWN. For example:

X'A3D0' = X'A3D0'

returns TRUE.

X'A3D0' <> {result is NULL}

returns UNKNOWN.

When a BLOB is compared to another BLOB, the comparison is done one octet at a time, from left to right – that is, the first comparand’s most significant octet is compared to the second comparand’s most significant octet, then the next two octets are compared, and so on. Two BLOBs, blob_argument_1 and blob_argument_2, are equal if (a) they have the same length and (b) each octet within blob_argument_1 compares as equal to the corresponding octet in blob_argument_2.

If you want to restrict your code to Core SQL, don’t use BLOBs in comparisons.

Other Operations¶

With SQL, you have several other operations that you can perform on BLOBs.

Concatenation ¶

The required syntax for a BLOB concatenation is as follows.

BLOB concatenation ::=
BLOB operand_1 || BLOB operand_2

The concatenation operator operates on two operands, both of which must evaluate to a BLOB. It joins the binary strings together in the order given and returns a BLOB with a length equal to the sum of the lengths of its operands. If either of the operands is NULL, the result of the operation is also NULL. Here are two examples of BLOB concatenations:

X'0000' || X'0011'     -- returns 00000011
blob_column || X'0011' -- returns blob_column's value followed by 0011

[Obscure Rule] If the sum of the lengths of a BLOB concatenation’s operands is not greater than the maximum allowed length for a BLOB, the concatenation result is a BLOB with a length equal to the sum of the lengths of the operands. If the sum of the operands’ lengths is greater than the maximum allowed, but the extra octets are all 0-octets, the concatenation result is a BLOB with a length equal to the maximum allowed length. If the sum of the operands’ lengths is greater than the maximum allowed and the extra octets are not all 0-octets, the concatenation will fail: your DBMS will return the SQLSTATE error 22001 "data exception-string data, right truncation".

If you want to restrict your code to Core SQL, don’t use the concatenation operator with BLOBs.

Scalar Operations ¶

SQL provides five scalar functions that return a BLOB: the <case expression>, the <cast specification>, the <BLOB substring function>, the <BLOB overlay function> and the <BLOB trim function>. It also provides four scalar functions that operate on BLOBs, returning a number: the <BLOB position expression>, the <bit length expression>, the <char length expression> and the <octet length expression>. All but the first two are described below. We’ll discuss the rest in other chapters; for now, just remember tat CASE can evaluate to a binary string and can therefore be used anywhere in an SQL statement that a binary string could be used.

<BLOB substring function>¶

The required syntax for a <BLOB substring function> is as follows.

<BLOB substring function> ::=
SUBSTRING (blob_argument
   FROM start_argument [ FOR length_argument ])

SUBSTRING operates on three arguments: the first must evaluate to a BLOB, the other two must evaluate to exact numeric integers. It extracts a substring from blob_argument and returns a BLOB with a maximum length that equals the maximum length of the BLOB argument. If any of the arguments are NULL, SUBSTRING returns NULL.

The start_argument is a number that marks the first octet you want to extract from blob_argument. If SUBSTRING includes the (optional) FOR clause, length_argument is the total number of octets you want to extract. If you omit the FOR clause, SUBSTRING will begin at start_argument and extract all the rest of the octets from blob_argument. Here are some examples of SUBSTRING:

SUBSTRING(X'1049FE2996D54AB7' FROM 5)        -- returns 96D54AB7
SUBSTRING(X'1049FE2996D54AB7' FROM 5 FOR 3)  -- returns 96D54A
SUBSTRING(blob_column FROM 1 FOR 4)          -- returns the first four
                                                octets of the value in
                                                BLOB_COLUMN

If length_argument is negative, your DBMS will return SQLSTATE error 22011 "data exception-substring error". If start_argument is greater than the length of blob_argument, or if (start_argument + length_argument) is less than one. SUBSTRING returns a zero-length binary string. If start_argument is negative, of if blob_argument, that´s okay – the DBMS just ignores any ocets before the start of blob_argument or after the end of blob_argument.

[Obscure Rule] SUBSTRING can also operate on a bit string and a character string. We’ve ignored these options for now – look for them in our chapters on bit strings and character strings.

If you want to restrict your code to Core SQL, don’t use SUBSTRING with BLOBs.

<BLOB overlay function>¶

The required syntax for a <BLOB overlay function> is:

<BLOB overlay function> ::=
OVERLAY (blob_argument_1 PLACING blob_argument_2
  FROM start_argument [ FOR length_argument ])

OVERLAY operates on four arguments: the first two must evaluate to BLOBs, the other two must evaluate to exact numeric integers. It extracts a substring from blob_argument_1, replacing it with blob_argument_2, and returns the resulting BLOB. If any of the arguments are NULL, OVERLAY returns NULL.

The start_argument is a number that marks the first octet you want to replace in blob_argument_1. If OVERLAY includes the (optional) FOR clause, length_argument is the total number of octets you want to replace. Thus, start_argument and length_argument identify the portion of blob_argument_1 that you want to replace, while blob_argument_2 is what you want to replace with. If you omit the FOR clause, then length_argument defaults to the length of blob_argument_2. Here are some examples of OVERLAY:

OVERLAY(X'1049FE2996D54AB7' PLACING X'1010' FROM 5)
 -- returns 1049FE2910104AB7
OVERLAY(X'1049FE2996D54AB7' PLACING X'1010' FROM 5 FOR 1)
 -- returns 1049FE291010D54AB7

[Obscure Rule] OVERLAY can also operate on a character string. We’ve ignored this option for now – look for it in our chapter on character strings.

<BLOB trim function>¶

The required syntax for a <BLOB trim function> is as follows.

<BLOB trim function> ::=
TRIM ( [ [ { LEADING | TRAILING | BOTH } ]
      [ blob_argument_1 ] FROM ]
      blob_argument_2)

TRIM operates on two arguments, both of which must evaluate to BLOBs. It strips all leading, all trailing or all leading and all trailing trim octets from blob_argument_2 and returns the resulting BLOB. If any of the arguments are NULL, TRIM returns NULL.

The trim specification is either LEADING, i.e., trim all leading trim octets, TRAILING, i.e., trim all trailing trim octets. If this clause is omitted, TRIM defaults to BOTH. For example, these two TRIM functions are equivalent; they both strip away leading and all trailing zero-ocets:

TRIM(X'00' FROM blob_column)
TRIM(BOTH X'00' FROM blob_column)

blob_argument_1 defines the trim octet: the octet that should be stripped away by the TRIM function. If blob_argument_1 is omitted, TRIM strips zero-octets away. For example, these two TRIM functions are equivalent: they both strip away all trailing zero-octets:

TRIM(TRAILING FROM blob_column)
TRIM(TRAILING X'00' FROM blob_column)

These two TRIM functions are equivalent: they both strip away all leading zero-octets:

TRIM(LEADING FROM blob_column)
TRIM(LEADING X'00' FROM blob_column)

These two TRIM functions are equivalent: they both strip away all leading and all trailing zero-octets:

TRIM(blob_column)
TRIM(BOTH X'00' FROM blob_column)

If the length of blob_argument_1 is not one octet, TRIM will fail: your DBMS will return the SQLSTATE error 22027 "data exception-trim error".

[Obscure Rule] TRIM can also operate on a character string. We’ve ignored this option for now – look for it in our chapter on character strings.

<BLOB position expression>¶

The required syntax for a <BLOB position expression> is as follows.

<BLOB position expression> ::=
POSITION (blob_argument_1 IN blob_argument_2)

POSITION operates on two arguments, both of which must evaluate to a BLOB. It determines the first octet position (if any) at which blob_argument_1 is found in blob_argument_2 and returns this as an exact numeric integer. If either of the arguments are NULL, POSITION returns NULL. If blob_argument_1 is a zero-length binary string, POSITION returns one. If blob_argument_1 is not found in blob_argument_2, POSITION returns zero. Here is an example:

POSITION(X'3D' IN X'AF923DA7')
-- returns 5

[NON-PORTABLE] The precision of POSITION’s result is non-standard because the SQL Standard requires implementors to define the result’s precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of POSITION an INTEGER <data type>.

[Obscure Rule] POSITION can also operate on a bit string and a character string. We’ve ignored these options for now – look for them in our chapters on bit strings and character strings.

<bit length expression>¶

The required syntax for a <bit length expression> is as follows.

<bit length expression> ::=
BIT_LENGTH (blob_argument)

BIT_LENGTH operates on an argument that evaluates to a BLOB. It determines the length of the argument, in bits, and returns this as an exact numeric integer, e.g., BIT_LENGTH(X'4AD9') returns 16. If the argument is NULL, BIT_LENGTH returns NULL.

[NON-PORTABLE] The precision of BIT_LENGTH’s result is non-standard because the SQL Standard requires implementors to define the result’s precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of BIT_LENGTH an INTEGER <data type>.

[Obscure Rule] BIT_LENGTH can also operate on a bit string and a character string. We’ve ignored these options for now – look for them in our chapters on bit strings and character strings.

<char length expression>¶

The required syntax for a <char length expression> is as follows.

<char length expression> ::=
{CHAR_LENGTH | CHARACTER_LENGTH} (blob_argument)

CHAR_LENGTH (or CHARACTER_LENGTH) operates on an argument that evaluates to a BLOB. It determines the length of the argument, in octets, and returns this as an exact numeric integer, e.g., CHAR_LENGTH(X'4AD9') returns 2. (The octet length of a string is the bit length divided by 8, rounded up.) If the argument is NULL, CHAR_LENGTH returns NULL.

[NON-PORTABLE] The precision of CHAR_LENGTH’s result is non-standard because the SQL Standard requires implementors to define the result’s precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of CHAR_LENGTH an INTEGER <data type>.

[Obscure Rule] CHAR_LENGTH can also operate on a bit string and a character string. We’ve ignored these options for now – look for them in our chapters on bit strings and character strings.

<octet length expression>¶

The required syntax for a <octet length expression> is as follows.

<octet length expression> ::=
OCTET_LENGTH (blob_argument)

OCTET_LENGTH operates on an argument that evaluates to a BLOB. It determines the length of the argument, in octets, and returns this as an exact numeric integer, e.g., OCTET_LENGTH(X'4AD9') returns 2. (The octet length of a string is the bit length divided by 8, ignoring any remainder.) If the argument is NULL, OCTET_LENGTH returns NULL.

[NON-PORTABLE] The precision of OCTET_LENGTH’s result is non-standard because the SQL Standard requires implementors to define the result’s precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book gives the result of OCTET_LENGTH an INTEGER <data type>.

[Obscure Rule] OCTET_LENGTH can also operate on a bit string and a character string. We’ve ignored these options for now – look for them in our chapters on bit strings and character strings.

Set functions ¶

SQL provides three set functions that operate on binary strings: COUNT and GROUPING. Since none of these operate exclusively with binary string arguments, we won’t discuss them here; look for them in our chapter on set functions.

Predicates ¶

In addition to the = and <> comparison operators, SQL provides four other predicates that operate on BLOBs: the <like predicate>, the <null predicate>, the <exists predicate> and the <quantified predicate>. Each will return a boolean value: either TRUE, FALSE or UNKNOWN. Only the first predicate operates strictly on string values; we’ll discuss it here. Look for the rest in our chapter on search conditions.

<like predicate>¶

The required syntax for a <like predicate> is as follows.

<like predicate> ::=
blob_argument [ NOT ] LIKE pattern [ ESCAPE escape_octet ]

LIKE is a predicate that operates on three operands that evaluate to BLOBs: it searches for values that contain a given pattern. NOT LIKE is the converse and lets you search for values that don’t contain a given pattern. The blob_argument is the binary string you’re searching within, the pattern is the pattern you’re searching for and the optional escape_octet is an octet that tells your DBMS to treat a metacharacter in the pattern as itself (rather than as a metacharacter). If blob_argument contains the pattern, LIKE returns TRUE and NOT LIKE returns FALSE. If blob_argument does not contain the pattern, LIKE returns FALSE and NOT LIKE returns TRUE. If any of the operands are NULL, LIKE and NOT LIKE return UNKNOWN.

The pattern you specify in pattern may contain any combination of regular octets and metaoctets. Any single octet in pattern that is not a metaoctet or the escape_octet represents itself in the pattern. For example, this predicate:

blob_column LIKE X'A3'

is TRUE for the octet represented by ‘A3’.

Special significance is attached to metaoctets in a pattern. The metaoctets are equivalent to the _ and % characters. That is, an underscore metaocet has the same bit pattern as an underscore character in the SQL_TEXT Character set and a percent octet has the same bit pattern as a percent sign in the SQL_TEXT Character set.( In practice, the bit pattern for _ will be X'5F' and the bit pattern for % will be X'25'. Thtese values correspond to the encodings used in all the ISO character sets.)

If the predicate doesn’t include an ESCAPE clause, they are interpreted as follows:

An underline octet means “any single octet”. For example, this predicate:

blob_column LIKE X' AA5FCC'

is TRUE for X'AAOOCC', X'AAAACC', X'AABBCC', X'AA66CC' and so on.

A percent sign means “any string of zero or more octets”. For example, this predicate:

blob_column LIKE X'AA25CC'

is TRUE for X'AACC', X'AAOOCC', X'AABBCC', X'AA66CC', X'AA6666CC' and so on.

If you want to search for an octet that would normally be interpreted as a metaoctet, you must use the optional ESCAPE clause. To do so:

Pick an octet that you won’t need in the pattern and designate it as your escape octet.
In the pattern, use your escape octet followed immediately by the metaoctet, to designate the metaoctet as an octet you want to search for. For example:

... LIKE X'BB25'

(without an ESCAPE clause) means “like the ocet BB followed by anything at all”, while:

... LIKE X'BBFF25' ESCAPE X'FF'

means “like the ocet BB followed by the octet 25” (because 25 is preceded by the escape ocet has no special significance in this pattern). Your escape ocet can also be followed by itself in the pattern, if you want to search for the escape character. For example:

... LIKE X'BBFFFF' ESCAPE X'FF'

means “like the ocet BB followed by the ocet FF” (since FF is preceded by the escape ocet it has no special significance in this pattern).

The escape_octet must be exactly one octet long. If it isn’t, [NOT] LIKE will fail: your DBMS will return the SQLSTATE error 2200D "data exception-invalid escape octet". If escape_octet is underscore metaocet or percent metaocet and that metaoctet is used once only in your pattern, or if escape_octet is used without being followed by a metaoctet (or by itself) in your pattern, [NOT] LIKE will fail: your DBMS will return the SQLSTATE error 22025 "data exception-invalid escape sequence". For example, these two predicates will both result in SQLSTATE 22025:

LIKE X'B%B' ESCAPE X'25'
LIKE X'B?B' ESCAPE X'FF'

For the purposes of [NOT] LIKE, a substring of blob_argument is a sequence of zero or more contiguous octets, where each octet belongs to exactly one such substring. A substring specifier of pattern is either (a) the underscore metaocet, an arbitrary octet specifier, (b) th percent metaocet, an arbitrary string specifier, (c) escape_octet followed by the underscore metaocet or the percent metaocet or the escape octet or (d) any other single octet. If blob_argument and pattern both have a length of zero, LIKE returns TRUE. LIKE also returns TRUE if pattern is found in blob_argument. That is, LIKE returns TRUE only if the number of substrings in blob_argument equals the number of substring specifiers in “pattern” and all of these conditions are also met:

If the pattern’s n-th substring specifier is the underscore metaocet, then the argument’s n-th substring must be any single octet.
If the pattern’s n-th substring specifier is percent metaocet, then the argument’s n-th substring must be any sequence of zero or more octets.
If the pattern’s n-th substring specifier is any other octet, then the argument’s n-th substring must have the same length and bit pattern as that substring specifier.

[Obscure Rule] [NOT] LIKE can also operate on character strings. We’ve ignored this option for now – look for it in our chapter on character strings.

If you want to restrict your code to Core SQL, don’t use the [NOT] LIKE predicate with BLOBs.

Chapter 5 – Binary strings¶

Subscribe to the CrateDB Newsletter now