Chapter 5 – Binary strings¶

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.

<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.