15.00 - CHARACTER_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)

CHARACTER_LENGTH

Purpose  

Returns the length of a string either in logical characters or in bytes.

Syntax  

where:

 

Syntax element …

Specifies …

string_expression

the string expression for which the length is to be returned.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes  

CHARACTER_LENGTH is the ANSI form of the Teradata CHARACTERS function. Use CHARACTER_LENGTH instead of CHARACTERS for ANSI SQL:2011 conformance.

Use CHARACTER_LENGTH in place of MCHARACTERS. (MCHARACTERS no longer appears in this book because its use is deprecated and it will not be supported after support for KANJI1 is dropped.)

Argument Types

The type of string_expression must be CHARACTER, VARCHAR, or CLOB. For non-character data types, the function returns an error.

By default, Teradata Database performs implicit type conversion on a UDT argument that has an implicit cast that casts between the UDT and 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 CHARACTER_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.”

Result

For all server character sets except KANJI1, CHARACTER_LENGTH returns the length of string_expression in characters.

For KANJI1, the following results are obtained.

 

FOR this client character set …

CHARACTER_LENGTH returns …

KanjiEBCDIC

the length of string_expression as the number of bytes.

A mix of single and multibyte characters is expected.

If any Shift-Out/Shift-In characters are present, they are included in the result count.

KanjiEUC
KanjiShift-JIS

the length of string_expression as the number of logical characters, based on the client session character set.

A mix of single and multibyte characters is expected.

ASCII
EBCDIC

the length of string_expression as the number of bytes.

Caution:

In accordance with Teradata internationalization plans, KANJI1 support is deprecated and is to be discontinued in the near future. KANJI1 is not allowed as a default character set; the system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible. “”

Because trailing pad characters are considered characters, the length of the value in a CHARACTER column is always equal to the length defined for the column.

The length of the value in a VARCHAR or CLOB column is always equal to the number of characters, including any trailing pad characters, contained in that value.

Suppressing Trailing Pad Characters

To suppress trailing pad characters from the character count for a data value, use the TRIM function on the argument to CHARACTER_LENGTH. For example:

   SELECT CHARACTER_LENGTH( TRIM( TRAILING FROM Name ) ) 
   FROM Employee;

Example  

The following statement applies the CHARACTER_LENGTH function to the Name column, which is type VARCHAR(30) CHARACTER SET LATIN, to obtain the number of characters in each employee name:

   SELECT Name, CHARACTER_LENGTH(Name) 
   FROM Employee; 

The result is as follows (note that separator blanks are considered characters):

   Name      Character_Length(Name)
   --------  ----------------------
   Smith T            7
   Newman P           8
   Omura H            7
         .            .

Example Set 1: KanjiEBCDIC

 

FOR this server character set …

AND example …

CHARACTER_LENGTH returns …

GRAPHIC

ABC

3

KANJI1

De<MNP>

10

<><>

4

Example Set 2: KanjiShift‑JIS

 

FOR this server character set …

AND example …

CHARACTER_LENGTH returns …

KANJI1

<><>

10

DeF

3

UNICODE

ABC

3

GRAPHIC

ABC

3

Example Set 3: KanjiEUC

 

FOR this server character set …

AND example …

CHARACTER_LENGTH returns …

KANJI1

ss3Css3D

2

GRAPHIC

2

UNICODE

<><>

0

dA ss2B ss3E

4

LATIN

ABC

3