SQL Server LEN() Function

 The LEN() function in SQL Server is used to return the number of characters in a string expression. It excludes trailing spaces from the count but includes leading and in-between spaces.

Syntax

LEN(string_expression)

• string_expression: The string whose length you want to determine. It can be a literal, a variable, or a column value.

Key Points

1. Trailing spaces are not included in the length.
2. Leading spaces and spaces between characters are counted.

Example Usage

Basic Example

SELECT LEN('Hello World') AS StringLength;

Result: 11

Leading and Trailing Spaces

SELECT LEN('  Hello  ') AS StringLength;

Result: 7
(Trailing spaces are ignored, but leading spaces are included.)

Column Usage

CREATE TABLE SampleTable (
    Name NVARCHAR(50)
);

INSERT INTO SampleTable (Name)
VALUES ('Alice'), ('  Bob  '), ('Charlie');

SELECT Name, LEN(Name) AS StringLength
FROM SampleTable;

Output:

Name	StringLength
Alice	5
Bob	5
Charlie	7

Compare with DATALENGTH

If you need the length in bytes (including trailing spaces), use the DATALENGTH() function instead. For example:

SELECT DATALENGTH('  Hello  ') AS DataLength, LEN('  Hello  ') AS StringLength;

Result:

• DATALENGTH: 9 (includes trailing spaces)
• LEN: 7 (excludes trailing spaces)

Let me know if you want more details or examples!