SQL Server/T-SQL Tutorial/System Functions/DATALENGTH
Содержание
DATALENGTH Versus COL_LENGTH to determine length of a string value.
<source lang="sql">
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</source>
SELECT DATALENGTH(@Value2)
<source lang="sql">
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)</source>
The DATALENGTH() function returns the number of bytes used to manage a value.
<source lang="sql">
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)</source>
Using DATALENGTH to determine length of a string value.
<source lang="sql">
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)</source>
