FirebirdSQL logo

OCTET_LENGTH Examples

select octet_length('Hello!') from rdb$database
-- returns 6

select octet_length(_iso8859_1 'Grüß di!') from rdb$database
-- returns 8: ü and ß take up one byte each in ISO8859_1

select octet_length
  (cast (_iso8859_1 'Grüß di!' as varchar(24) character set utf8))
from rdb$database
-- returns 10: ü and ß take up two bytes each in UTF8

select octet_length
  (cast (_iso8859_1 'Grüß di!' as char(24) character set utf8))
from rdb$database
-- returns 26: all 24 CHAR positions count, and two of them are 2-byte

OVERLAY()

Overwrites part of, or inserts into, a string

Result type

VARCHAR or BLOB

Syntax
OVERLAY (string PLACING replacement FROM pos [FOR length])
Table 1. OVERLAY Function Parameters
Parameter Description

string

The string into which the replacement takes place

replacement

Replacement string

pos

The position from which replacement takes place (starting position)

length

The number of characters that are to be overwritten

By default, the number of characters removed from (overwritten in) the host string equals the length of the replacement string.With the optional fourth argument, a different number of characters can be specified for removal.

  • This function supports BLOBs of any length.

  • If string or replacement is a BLOB, the result is a BLOB.Otherwise, the result is a VARCHAR(n) with n the sum of the lengths of string and replacement.

  • As usual in SQL string functions, pos is 1-based.

  • If pos is beyond the end of string, replacement is placed directly after string.

  • If the number of characters from pos to the end of string is smaller than the length of replacement (or than the length argument, if present), string is truncated at pos and replacement placed after it.

  • The effect of a “FOR 0” clause is that replacement is inserted into string.

  • If any argument is NULL, the result is NULL.

  • If pos or length is not a whole number, bankers' rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

Warning

When used on a BLOB, this function may need to load the entire object into memory.This may affect performance if huge BLOBs are involved.

OVERLAY Examples

overlay ('Goodbye' placing 'Hello' from 2)   -- returns 'GHelloe'
overlay ('Goodbye' placing 'Hello' from 5)   -- returns 'GoodHello'
overlay ('Goodbye' placing 'Hello' from 8)   -- returns 'GoodbyeHello'
overlay ('Goodbye' placing 'Hello' from 20)  -- returns 'GoodbyeHello'

overlay ('Goodbye' placing 'Hello' from 2 for 0) -- r. 'GHellooodbye'
overlay ('Goodbye' placing 'Hello' from 2 for 3) -- r. 'GHellobye'
overlay ('Goodbye' placing 'Hello' from 2 for 6) -- r. 'GHello'
overlay ('Goodbye' placing 'Hello' from 2 for 9) -- r. 'GHello'

overlay ('Goodbye' placing '' from 4)        -- returns 'Goodbye'
overlay ('Goodbye' placing '' from 4 for 3)  -- returns 'Gooe'
overlay ('Goodbye' placing '' from 4 for 20) -- returns 'Goo'

overlay ('' placing 'Hello' from 4)          -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 0)    -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 20)   -- returns 'Hello'

POSITION()

Finds the position of the first or next occurrence of a substring in a string

Result type

INTEGER

Syntax
  POSITION (substr IN string)
| POSITION (substr, string [, startpos])
Table 1. POSITION Function Parameters
Parameter Description

substr

The substring whose position is to be searched for

string

The string which is to be searched

startpos

The position in string where the search is to start

Returns the (1-based) position of the first occurrence of a substring in a host string.With the optional third argument, the search starts at a given offset, disregarding any matches that may occur earlier in the string.If no match is found, the result is 0.

Note
  • The optional third argument is only supported in the second syntax (comma syntax).

  • The empty string is considered a substring of every string.Therefore, if substr is '' (empty string) and string is not NULL, the result is:

    • 1 if startpos is not given;

    • startpos if startpos lies within string;

    • 0 if startpos lies beyond the end of string.

  • This function fully supports text BLOBs of any size and character set.

Warning

When used on a BLOB, this function may need to load the entire object into memory.This may affect performance if huge BLOBs are involved.