15.00 - OCTET_LENGTH - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

OCTET_LENGTH

Purpose  

Returns the length of string_expression in octets when it is converted to the named character set (taking the export width value into consideration).

Syntax  

where:

 

Syntax element …

Specifies …

string_expression

the character string for which the number of octets is required.

character_set_name

the character set in which the result is to be returned. If character_set_name is not provided, the session character set is assumed.

See the list of Teradata-provided character sets in the table on “Usage Notes” on page 297.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Argument Types

The data type of string_expression must be one of the following:

  • CHARACTER or VARCHAR
  • UDT that has an implicit cast to a predefined character type
  • To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit type conversion of UDTs for system operators and functions, including OCTET_LENGTH, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

    For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”

    Usage Notes  

    Any Shift-Out/Shift-In and trailing GRAPHIC pad characters are included in the result count.

    OCTET_LENGTH operates in the same manner in both Teradata and ANSI modes.

     

    IF string_expression is …

    THEN …

    of type KANJI1

    the result is independent of character_set_name.

    not CHARACTER data

    an error is generated.

    OCTET_LENGTH takes the export width value into consideration. For information about export width, see “CREATE USER” in SQL Data Definition Language.

    The following table lists the client character sets shipped with Teradata. Although these character sets are shipped with the system, your system administrator must install them individually to become available for use.

    Your site might also have site-defined character sets. Check with your system administrator for a complete list of character sets available at your site.

     

    Character Sets

    Where Found

  • ASCII
  • EBCDIC
  • UTF8
  • UTF16
  • Built-in

  • ARABIC1256_6A0 1
  • CYRILLIC1251_2A0 a
  • EBCDIC037_0E
  • EBCDIC273_0E
  • EBCDIC277_0E
  • HANGUL949_7R0 a
  • HANGULEBCDIC933_1II
  • HANGULKSC5601_2R4
  • HEBREW1255_5A0 a
  • KANJI932_1S0 a
  • KANJIEBCDIC5026_0I
  • KANJIEBCDIC5035_0I
  • KANJIEUC_0U
  • KANJISJIS_0S
  • KATAKANAEBCDIC
  • LATIN1250_1A0 a
  • LATIN1252_0A
  • LATIN1252_3A0 a
  • LATIN1254_7A0 a
  • LATIN1258_8A0 a
  • LATIN1_0A
  • LATIN9_0A
  • SCHEBCDIC935_2IJ
  • SCHGB2312_1T0
  • SCHINESE936_6R0 a
  • TCHBIG5_1R0
  • TCHEBCDIC937_3IB
  • TCHINESE950_8R0 a
  • THAI874_4A0 a
  • DBC.CharTranslationsV

     


    1
    Windows code page compatible session character set

    Examples  

    Examples of output from OCTET_LENGTH appear in the following table.

     

    Client Character Set

    Server Character Set

    string_expression

    Result

    EBCDIC

    LATIN

    abcdefgh

    8

    ASCII

    KANJI1

    abcdefgh

    8

    KanjiEBCDIC

    KANJI1

    AB<CDE>P

    11

    KanjiEBCDIC

    GRAPHIC

    MNOP

    8 (record mode)

    10 (field mode)

    KanjiEUC

    KANJISJIS

    dA ss2B ss3E

    8

    KanjiShift-JIS

    KANJISJIS

    DeF

    5

    KanjiShift-JIS

    UNICODE

    ABC

    6