char_length

Description

Returns the number of characters in an expression.

Syntax

char_length(char_expr | uchar_expr)

Parameters

char_expr: is a character-type column name, variable, or constant expression of char, varchar, nchar, text_locator, unitext_locator, or nvarchar type.

uchar_expr: is a character-type column name, variable, or constant expression of unichar or univarchar type.

Examples

Example 1

select char_length(notes) from titles
    where title_id = "PC9999"

 -----------
          39

Example 2

declare @var1 varchar(20), @var2 varchar(20), @char char(20)
    select @var1 = "abcd", @var2 = "abcd    ", @char = "abcd"
    select char_length(@var1), char_length(@var2), char_length(@char)

 ----------- ----------- ----------- 
           4           8          20

Usage

char_length, a string function, returns an integer representing the number of characters in a character expression or text value.

For compressed large object (LOB) columns, char_length returns the number of original plain text characters.

For variable-length columns and variables, char_length returns the number of characters (not the defined length of the column or variable). If explicit trailing blanks are included in variable-length variables, they are not stripped. For literals and fixed-length character columns and variables, char_length does not strip the expression of trailing blanks (see Example 2).

For unitext, unichar, and univarchar columns, char_length returns the number of Unicode values (16-bit), with one surrogate pair counted as two Unicode values. For example, this is what is returned if a unitext column ut contains row value U+0041U+0042U+d800dc00:

select char_length(ut) from unitable
------------
4

For multibyte character sets, the number of characters in the expression is usually fewer than the number of bytes; use datalength to determine the number of bytes.
For Unicode expressions, returns the number of Unicode values (not bytes) in an expression. Surrogate pairs count as two Unicode values.
If char_expr or uchar_expr is NULL, char_length returns NULL.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute char_length.