Simple text functions
How to modify Strings


Okay. You can find out how many characters are in a string. Can we also count the number of bytes in a string?

Yes! T-SQL uses the DATALENGTH() function to do that. It has only one parameter: the column name. DATALENGTH() returns the number of bytes in the value stored in this field. It is similar to the LEN() function, but DATALENGTH() can be used with any data type; LEN() only works with text. Another difference is that DATALENGTH() returns the number of bytes, while LEN() returns the number of characters.

Let's start with finding out how many bytes occupy each Id and Text value in the Slogan table.

  DATALENGTH(Text) AS TextLength
FROM Slogan;
IdLength TextLength
4 74
4 80
... ...
4 48

As you can see, each value in the Text column takes a different amount of bytes, while all Ids have the same. The longer text occupies more bytes, while the shorter text occupies fewer bytes.


Are you curious about how many bytes are in every copywriter's first name? Select the FirstName column from the Copywriter table and use DATALENGTH() to find out. Show both columns. Name the second column ByteLength.

Observe that passing in a NULL to this function returns a NULL.

Stuck? Here's a hint!

Use DATALENGTH(FirstName).