в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:03Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/System Functions/DATALENGTH - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия