LEN (Transact-SQL)LEN (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

返回指定字符串表达式的字符数,其中不包含尾随空格。Returns the number of characters of the specified string expression, excluding trailing spaces.

备注

若要返回用于表示表达式的字节数,请使用 DATALENGTH 函数。To return the number of bytes used to represent an expression, use the DATALENGTH function.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

LEN ( string_expression )  

参数Arguments

string_expression string_expression
要计算的字符串表达式Is the string expression to be evaluated. string_expression 可以是常量、变量,也可以是字符列或二进制数据列 。string_expression can be a constant, variable, or column of either character or binary data.

返回类型Return Types

bigint(如果 expression 的数据类型为 varchar(max)、nvarchar (max) 或 varbinary(max));否则为 int 。bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

如果使用 SC 排序规则,则返回的整数值将 UTF-16 代理项对作为单个字符计数。If you are using SC collations, the returned integer value counts UTF-16 surrogate pairs as a single character. 有关详细信息,请参阅 排序规则和 Unicode 支持For more information, see Collation and Unicode Support.

备注Remarks

LEN 不包括尾随空格。LEN excludes trailing spaces. 如果这是个问题,请考虑使用 DATALENGTH (Transact-SQL) 函数,该函数不会修整字符串。If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. 如果处理的是 unicode 字符串,DATALENGTH 会返回可能不等于字符数的数字。If processing a unicode string, DATALENGTH will return a number that may not be equal to the number of characters. 以下示例演示 LEN 和带有尾随空格的 DATALENGTH。The following example demonstrates LEN and DATALENGTH with a trailing space.

DECLARE @v1 varchar(40),  
    @v2 nvarchar(40);  
SELECT   
@v1 = 'Test of 22 characters ',   
@v2 = 'Test of 22 characters ';  
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];  
SELECT LEN(@v2) AS [nvarchar LEN], DATALENGTH(@v2) AS [nvarchar DATALENGTH];  

备注

使用 LEN 返回编码到给定字符串表达式中的字符数,使用 DATALENGTH 返回给定字符串表达式的字节大小。Use the LEN to return the number of characters encoded into a given string expression, and DATALENGTH to return the size in bytes for a given string expression. 这些输出可能会因列中使用的数据类型和编码类型而异。These outputs may differ depending on the data type and type of encoding used in the column. 若要详细了解不同编码类型的存储区别,请参阅排序规则和 Unicode 支持For more information on storage differences between different encoding types, see Collation and Unicode Support.

示例Examples

以下示例在 FirstName 地区的人的 Australia 中选择字符数和数据。The following example selects the number of characters and the data in FirstName for people located in Australia. 本示例使用 AdventureWorks 数据库。This example uses the AdventureWorks database.

SELECT LEN(FirstName) AS Length, FirstName, LastName   
FROM Sales.vIndividualCustomer  
WHERE CountryRegionName = 'Australia';  
GO  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

以下示例返回 FirstName 列中的字符数,以及位于 Australia 中的员工的姓氏和名字。The following example returns the number of characters in the column FirstName and the first and last names of employees located in Australia.

USE AdventureWorks2016  
GO  
SELECT DISTINCT LEN(FirstName) AS FNameLength, FirstName, LastName   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.DimGeography AS g   
    ON e.SalesTerritoryKey = g.SalesTerritoryKey   
WHERE EnglishCountryRegionName = 'Australia';  

下面是结果集:Here is the result set.

FNameLength  FirstName  LastName  
-----------  ---------  ---------------  
4            Lynn       Tsoflias

另请参阅See Also

DATALENGTH (Transact-SQL) DATALENGTH (Transact-SQL)
CHARINDEX (Transact-SQL)CHARINDEX (Transact-SQL)
PATINDEX (Transact-SQL)PATINDEX (Transact-SQL)
LEFT (Transact-SQL) LEFT (Transact-SQL)
RIGHT (Transact-SQL)RIGHT (Transact-SQL)
数据类型 (Transact-SQL) Data Types (Transact-SQL)
字符串函数 (Transact-SQL)String Functions (Transact-SQL)