-1

I have a column with the following data: SMITH SMITH SMITH SMITH SMITH. When doing a SELECT DATALENGTH from the table, I am getting a value of 72.

However when I copy the column data and pop it in the select statement like the below, I get a length of 36.

SELECT DATALENGTH('JUDITH KOSGEY JUDITH JEPKORIR KOSGEY')

What could be the possible reasoning for this? I do no see any extra spaces or special characters in this text.

John
  • 263
  • 1
  • 15
  • What do you get if you do `CONVERT(varbinary(150),YourColumn)`? – Thom A Dec 07 '22 at 14:38
  • @Larnu 0x4A005500440049005400480020004B004F00530047004500590020004A005500440049005400480020004A00450050004B004F0052004900520020004B004F005300470045005900 – John Dec 07 '22 at 14:41
  • 2
    I get 72 with `SELECT DATALENGTH(N'JUDITH KOSGEY JUDITH JEPKORIR KOSGEY');`. Note the Unicode constant prefix so it seems the column is `nvarchar`. – Dan Guzman Dec 07 '22 at 14:42
  • Well that isn't `'SMITH SMITH SMITH SMITH SMITH'`.... It's `N'JUDITH KOSGEY JUDITH JEPKORIR KOSGEY'`... Even if it were `'JUDITH KOSGEY JUDITH JEPKORIR KOSGEY'` that would return a `DATALENGTH` of `36` *not* `29`... – Thom A Dec 07 '22 at 14:43
  • @Larnu I've edited my question. I see I copied the incorrect value. It should be JUDITH KOSGEY JUDITH JEPKORIR KOSGEY – John Dec 07 '22 at 14:44
  • Your value is an `nvarchar`, not a `varchar`, @John , so you aren't actually comparing apples to apples. To get the correct `DATALENGTH` value for the literal you need to also use an `nvarchar` (as [Dan Guzman](https://stackoverflow.com/users/3711162/dan-guzman) shows in their [comment](https://stackoverflow.com/questions/74718350/sql-server-datalength-issues#comment131873622_74718350)). – Thom A Dec 07 '22 at 14:45

2 Answers2

1

If your column is NVARCHAR, it is intended to handle double byte character sets.

Use LEN(<column>) instead of DATALENGTH(<column>) and you will get the value you are expecting.

Dimi
  • 452
  • 1
  • 9
Mike B
  • 87
  • 5
0

This could be because the data column is UNICODE which takes more storage per character. What's the type of the column?

HSS
  • 178
  • 4
  • This alone wouldn't be enough, `N'SMITH SMITH SMITH SMITH SMITH'` would have a `DATALENGTH` of `58`. – Thom A Dec 07 '22 at 14:40