Mega Code Archive
DATALENGTH Versus COL_LENGTH to determine length of a string value
<![CDATA[
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
]]>