在子字符串中使用 datalength(varbinary datatype)

发布于 2024-10-15 19:08:35 字数 405 浏览 2 评论 0原文

我正在尝试从 TABLE(DATA varbinary(2048)) 选择一些 varbinary 数据到我的 .NET dll，而无需任何填充。我的所有记录目前都是 64 字节长，但将来可能会有所不同。

我使用一个存储过程来执行此操作：

 select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

我希望它能够工作，但是我在 dll 中获得的流的长度是 2050(2048 + 2) 字节。

当我对值进行硬编码时（select substring(DATA, 1, 64) as DATA from TABLE），它返回 66 个字节，正如我所期望的那样。

我错过了什么（明显的）吗？

原文

I am trying to select some varbinary data from TABLE(DATA varbinary(2048)) to my .NET dll without any padding. All my records are currently 64 bytes long but they may vary in the future.

I use a stored procedure that does this:

 select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

I would expect this to work but the stream I get in my dll is 2050(2048 + 2) bytes long.

When I hard code the value (select substring(DATA, 1, 64) as DATA from TABLE) it returns 66 bytes as I would expect.

Am I missing something (obvious)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

守护在此方 2024-10-22 19:08:35

select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

这是对 SQL Server 类型的误解。当您从 varbinary(2048) 列开始时，除非您将其延长，否则即使您减少实际数据内容，它也将保持在 2048 并且不会崩溃。

这表明 temptbl 中 DATA 的长度仍然是 2048，即使源表中只有一条记录并且长度为 64。

create table tbl(data varbinary(2048))
;
insert into tbl select convert(varbinary(2048),REPLICATE('a',64))
;
select substring(DATA, 1, datalength(Data)) as DATA
into temptbl
from tbl
;
exec sp_help temptbl

如果您确实想要，则需要使用动态 SQL 来调整输出列的大小，但很少需要做这样的事情。

declare @nsql nvarchar(max)
set @nsql = 'select convert(varbinary(' +
 right((select max(datalength(Data)) as MaxLen from tbl),12) +
 '), data) as Data from tbl'

select substring(DATA, 1, datalength(DATA)) as DATA from TABLE

This is a misunderstanding of SQL Server types. When you start off with a varbinary(2048) column, unless you make it longer, it will stay at 2048 and will not collapse even if you reduce the actual data content.

This shows you that the length of DATA in temptbl is still 2048, even though there is only one record in the source table and it is of length 64.

create table tbl(data varbinary(2048))
;
insert into tbl select convert(varbinary(2048),REPLICATE('a',64))
;
select substring(DATA, 1, datalength(Data)) as DATA
into temptbl
from tbl
;
exec sp_help temptbl

If you really wanted to, you would need to use dynamic SQL to size the output column, but there is very rarely any need to do such a thing.

declare @nsql nvarchar(max)
set @nsql = 'select convert(varbinary(' +
 right((select max(datalength(Data)) as MaxLen from tbl),12) +
 '), data) as Data from tbl'

回复收藏 0 原文

~没有更多了~