Stack Overflow
Stack Overflow

Questions tagged [datalength]

DATALENGTH is a T-SQL function in SQL Server 2005+ which takes any column or variable, and returns the length of the data in bytes.

DATALENGTH is a T-SQL function in SQL Server 2005+ which takes any column or variable, and returns the length of the data in bytes.

Usually returns an int, but can return a bigint if used with varchar(max), nvarchar(max) or varbinary(max) datatypes.

It is comparable to the LEN function, but LEN trims the input, whereas DATALENGTH does not.

41 questions
94
votes
9 answers

SQL Server 2008 Empty String vs. Space

I ran into something a little odd this morning and thought I'd submit it for commentary. Can someone explain why the following SQL query prints 'equal' when run against SQL 2008. The db compatibility level is set to 100. if '' = ' ' print…
jhale
  • 1,790
  • 3
  • 14
  • 21
59
votes
2 answers

What is the size of a image field content in SQL Server?

I have a table in SQL Server. This table has an image field and the application stores files in it. Is there a way to read the size of the file in the image field using T-SQL?
Fabio
  • 1,037
  • 1
  • 10
  • 22
45
votes
6 answers

count number of characters in nvarchar column

Does anyone know a good way to count characters in a text (nvarchar) column in Sql Server? The values there can be text, symbols and/or numbers. So far I used sum(datalength(column))/2 but this only works for text. (it's a method based on datalength…
Sam
  • 3,067
  • 19
  • 53
  • 55
35
votes
6 answers

Len() vs datalength() in SQL Server 2005

Recently I faced a issue when using len() in a query to find out the length of a query, len() was not counting the trailing spaces in the value. But datalength() is counting the trailing spaces also. Does this means that if I'm doing some operation…
Arun P Johny
  • 384,651
  • 66
  • 527
  • 531
6
votes
1 answer

Length of varbinary(max) filestream on SQL Server 2008

Is there some efficient way how to get length of data in "varbinary(max) filestream" column? I found only samples with conversion to varchar and then calling the "LEN" function.
TcKs
  • 25,849
  • 11
  • 66
  • 104
5
votes
4 answers

In SQL Server 2005, what is the difference between len() and datalength()?

What is the difference between len() and datalength() in SQL Server 2005?
dplante
  • 2,445
  • 3
  • 21
  • 27
4
votes
2 answers

Difference between sp_spaceused and DataLength SQL Server

I have a table with single Row when i use SP_SpaceUsed N'' it gives me data as 16 KB and when I use dataLength something like this:- select ClientID , (0 + isnull(datalength(ClientID), 1) + isnull(datalength(LeadID), 1) +…
Sandhurst
3
votes
1 answer

size of a sql table variable without using DATALENGTH

How can I determine the space used by a table variable without using DATALENGTH on all columns? eg: DECLARE @T TABLE ( a bigint, b bigint, c int, d varchar(max) ) insert into @T select 1,2,3, 'abc123' exec sp_spaceused @T Trying to work out how…
Seph
  • 8,472
  • 10
  • 63
  • 94
3
votes
1 answer

why does sql server's datalength function double the length of my field?

I want to count the characters in an ntext field. Following Pinal Dave's advice, I am using datalength. But this function seems to double the value I am looking for. When I copy the value in the field into word and count the characters, I get 1502.…
bernie2436
  • 22,841
  • 49
  • 151
  • 244
2
votes
4 answers

Retrieving datalength along with columns and tables in SQL Server query

Because I want to convert the columns to not be varchar(MAX) I want to see the maximum datalength for each column to decide what the new size should be. I have this query for finding all my (n)varchar(MAX) columns. SELECT [TABLE_NAME],…
Daniel
  • 10,641
  • 12
  • 47
  • 85
2
votes
2 answers

varChar and Char behave different on cast

Why the output is different of this query SELECT DATALENGTH(CAST('test' AS VARCHAR)), DATALENGTH(CAST('test' AS CHAR)) outPut: 4,30
Jeevan Bhatt
  • 5,881
  • 18
  • 54
  • 82
2
votes
2 answers

Get length/size of json data

I have use the following codes to get my json data with certain nodes. console.log(Object.keys(data.Top["person"]).length); It is work fine in following structure of json data…
Elice Ee
  • 129
  • 1
  • 3
  • 11
2
votes
1 answer

LEN and DATALENGTH of VARCHAR and NVARCHAR

After reading "What is the difference between char, nchar, varchar, and nvarchar in SQL Server?" I have a question. I'm using MS SQL Server 2008 R2 DECLARE @T TABLE ( C1 VARCHAR(20) COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS, C2…
Marco
  • 21
  • 4
2
votes
3 answers

Adding datalength condition makes query slow

I have a table mytable with some columns including the column datekey (which is a date and has an index), a column contents which is a varbinary(max), and a column stringhash which is a varchar(100). The stringhash and the datekey together form the…
tomsv
  • 7,207
  • 6
  • 55
  • 88
1
vote
1 answer

Sum of DataLength values does not match table size

I am trying to find which user records in a table are taking up the most space. To this end, I am using the DATALENGTH function in SqlServer. SELECT UserName, SUM( ISNULL(DATALENGTH(columnA), 1) + …
webworm
  • 10,587
  • 33
  • 120
  • 217
1
2 3