Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in system functions – LEN() and DATALENGTH()

Using Built-in system functions – LEN() and DATALENGTH()


While LEN() is used to return the number of characters in a String, DATALENGTH() can be used to return the number of bytes used by any expression.

LEN() and DATALENGTH() functions are useful for variable length data types.

To demonstrate these functions let us create a table with different data type columns:

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE TABLE dbo.TempTable

(

      NameNVarchar      NVARCHAR(64),

      NameVarchar       VARCHAR(64),

      NameChar          CHAR(64)

)

INSERT INTO dbo.TempTable VALUES

('Lincoln Burrows', 'Lincoln Burrows', 'Lincoln Burrows')

 

INSERT INTO dbo.TempTable VALUES

('Michael Scofield', 'Michael Scofield', 'Michael Scofield')

In above example, the first columns will store only 32 characters as it will be stored using Unicode. Also, the first two columns are of variable-length. And, the third columns will required fixed 64-bytes.

Now, to find out the string length we can use LEN():

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      LEN(NameVarchar)  AS 'LEN(VARCHAR)',

            LEN(NameNVarchar) AS 'LEN(NVARCHAR)',

            LEN(NameChar)     AS 'LEN(CHAR)'

FROM        dbo.TempTable

Result Set:

LEN(VARCHAR) LEN(NVARCHAR) LEN(CHAR)

———— ————- ———–

15           15            15

16           16            16

 

(2 row(s) affected)

But, the storage size will be different from string length due to different data types, which can be retrieved using DATALENGTH():

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      DATALENGTH(NameVarchar)  AS 'DATALENGTH(VARCHAR)',

            DATALENGTH(NameNVarchar) AS 'DATALENGTH(NVARCHAR)',

            DATALENGTH(NameChar)     AS 'DATALENGTH(CHAR)'

FROM        dbo.TempTable

Result Set:

DATALENGTH(VARCHAR) DATALENGTH(NVARCHAR) DATALENGTH(CHAR)

——————- ——————– —————-

15                  30                   64

16                  32                   64

 

(2 row(s) affected)

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.