char_length

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 – Returns a number of characters from titles where the ID is PC9999:

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

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

Example 2 – Returns the number of characters from three variables:

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

For:

Compressed large object (LOB) columns, char_length returns the number of original plain text characters.
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).
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
```
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.
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.