SQL Server/T-SQL Tutorial/System Functions/DATALENGTH

DATALENGTH Versus COL_LENGTH to determine length of a string value.

5>
6> CREATE TABLE Composer (
7>      Composer_ID    integer  NOT NULL PRIMARY KEY,
8>      Composer      varchar(40)
9> )
10> GO
1> INSERT into Composer VALUES(100,"John")
2> INSERT into Composer VALUES(101,"Jade")
3> INSERT into Composer VALUES(102,"Queen")
4> INSERT into Composer VALUES(103,"Leppard")
5> INSERT into Composer VALUES(104,"Brooks")
6> INSERT into Composer VALUES(105,"Raye")
7> INSERT into Composer VALUES(106,"Street")
8> INSERT into Composer VALUES(107,"Chicago")
9> INSERT into Composer VALUES(108,"Outfield")
10> INSERT into Composer VALUES(109,"REO")
11> INSERT into Composer VALUES(110,"Cars")
12> INSERT into Composer VALUES(111,"Rick")
13> INSERT into Composer VALUES(112,"Genesis")
14> INSERT into Composer VALUES(113,"Pat")
15> INSERT into Composer VALUES(114,"Tears")
16> INSERT into Composer VALUES(115,"Mode")
17> INSERT into Composer VALUES(116,"Eagles")
18> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT Composer,DATALENGTH(Composer) "Actual_Width",
3>               COL_LENGTH("Composer","Composer")"Defined_Width"
4> FROM composer
5> ORDER BY DATALENGTH(Composer)
6> GO
Composer                                 Actual_Width Defined_Width
---------------------------------------- ------------ -------------
REO                                                 3            40
Pat                                                 3            40
Mode                                                4            40
Cars                                                4            40
Rick                                                4            40
John                                                4            40
Jade                                                4            40
Raye                                                4            40
Queen                                               5            40
Tears                                               5            40
Eagles                                              6            40
Street                                              6            40
Brooks                                              6            40
Chicago                                             7            40
Leppard                                             7            40
Genesis                                             7            40
Outfield                                            8            40
(17 rows affected)
1>
2> drop table Composer;
3> GO

SELECT DATALENGTH(@Value2)

5> DECLARE @Value1 Int, @Value2 Int
6> SET @Value1 = 2
7> SET @Value2 = 2000000000
8> SELECT DATALENGTH(@Value1)
9> SELECT LEN(@Value1)
10> SELECT DATALENGTH(@Value2)
11> SELECT LEN(@Value2)
12> GO
-----------
          4
(1 rows affected)
-----------
          1
(1 rows affected)
-----------
          4
(1 rows affected)
-----------
         10
(1 rows affected)

The DATALENGTH() function returns the number of bytes used to manage a value.

3>
4> DECLARE @Value VarChar(20)
5> SET @Value = "abc"
6> SELECT DATALENGTH(@Value)
7> SELECT LEN(@Value)
8> GO
-----------
          3
(1 rows affected)
-----------
          3
(1 rows affected)

Using DATALENGTH to determine length of a string value.

6>     SELECT UPPER(SUBSTRING("this Is a tEst.",1,1)) +
7>            LOWER(SUBSTRING("this Is a tEst.",2,
8>               (DATALENGTH("this Is a tEst.")-1)))
9>
10> GO
---------------
This is a test.
(1 rows affected)