Len()函数和 DataLength()函数的区别
Len()函数和 DataLength()函数的区别
http://www.sqlnotes.info/2013/05/16/len-and-datalength/
Both system functions, Len() and DataLength(), in SQL Server are used to measure the length of the data. The main difference of those 2 is that Len() gets the string length of the data in which DataLength measures the storage length of the data.
Len always converts the input to string and trim the ending, for instance
01
declare @x varchar(max), @x1 char(10), @x2 int
02
select @x = 'abc', @x1 = 'abc', @x2 = 999999
03
select len(@x) x, len(@x1) x1, len(@x2) x2
04
select datalength(@x) x, datalength(@x1) x1, datalength(@x2) x2
05
--Result
06
--x x1 x2
07
---------------------- ----------- -----------
08
--3 3 6
09
10
--x x1 x2
11
---------------------- ----------- -----------
12
--3 10 4
In this example x1 is an fixed length string. The ending spaces are trimmed by Len() but not trimmed by DataLength(). Some time this will cause some trouble if you mixed the concepts of those 2.
01
declare @x3 nvarchar(20), @x4 varbinary(20)
02
select @x3 = 'abc ' -- space at the end
03
select @x4 = 0x61626320 -- 4 byte long, abc + space
04
select len(@x3) x3, len(@x4) x4
05
select datalength(@x3) x3, datalength(@x4) x4
06
--Result
07
--x3 x4
08
------------- -----------
09
--3 3
10
11
--x3 x4
12
------------- -----------
13
--8 4
x3 is a string in unicode form. Every 2 bytes presents a character. Len() presents the “number of chars” of the string with trimmed ending spaces where DataLength() gives you 8, which is total storage of entire string.
x4 is a 4 byte binary. Len() converts it to string and trimmed the ending space, which is 0x20, and give you 3 where DataLength() give you the correct length of the data.
You can pass everything to DataLength(). A correct length of data in bytes will be returned, including sql_variant, CLR type, xml, etc…
But this is not the case for Len(). If the parameter cannot be explicitly converted to string, a weird error will be returned.(I think something like “Cannot implicitly convert…to string” will be more appropriate.)
1
declare @xml xml = '<a/>'
2
select len(@xml)
3
--Return
4
--Msg 8116, Level 16, State 1, Line 2
5
--Argument data type xml is invalid for argument 1 of len function.
John Huang, SQL Server MCM + MVP, http://sqlnotes.info
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
2013-10-25 带您理解SQLSERVER是如何执行一个查询的
2013-10-25 SQL Server 2000中的并行处理和执行计划中的位图运算符
2012-10-25 SQLSERVER model数据库