SQL Server Len() Function Vs. DataLength() Function
The LEN system
function returns the number of characters of the specified string
expression.
On the other hand, the DATALENGTH function returns the number of bytes used to represent any expression. Here’s a summary of the similarities and differences between the LEN() and DATALENGTH() system functions:
Similarities:
Differences:
LEN() returns the number of
characters in the string or variable while DATALENGTH() returns the number of bytes used to represent a
string or expression. We have 4 different cases in which we can
study these two functions.
Case
1: With "Varchar" Data types:
When a Varchar type is passed to both the DATALENGTH() and LEN() functions, they return the same value.
Example:
DECLARE @Value varchar(20)
SET @value = ‘Reddyz’ SELECT DATALENGTH(@value) SELECT LEN(@value) These statements both return 6 as the Varchar type uses 6 single bytes characters to store the 6-character value.
So in this case, both the
functions are returning the same.
Case 2: With "Nvarchar" Data
types:
If an nvarchar type is used, DATALENGTH() takes twice as many bytes to manage a value of the same length whereas LEN() returns the number of characters.
Example:
DECLARE @Value nvarchar(20) SET @value = ‘Reddyz’ SELECT DATALENGTH(@value) SELECT LEN(@value) The DATALENGTH() returns 12 because 2 bytes are used to store each character using a Unicode character set.
The LEN() returns 6 as this returns the number of
characters, not the number of bytes.
|
||||||||||
Case
3: With Integer Data types:
If integer Datatype is used,
DATALENGTH() returns 4 and LEN() returns the number of digits.
Example:
DECLARE @Value1 int, @value2 int
SET @value1=2 SET @value2 =20000 SELECT DATALENGTH(@value1) SELECT LEN(@value1) SELECT DATALENGTH(@value2) SELECT LEN(@value2)
The DATALENGTH() returns 4 in both the cases because int always
takes 4 bytes, whatever the value would be.
But the LEN() treats the integer value as if it were converted to a
character type and returns the number of digits. So results are 1 and 5
respectively.
Case
4: With Extra Spaces:
If string or variable value
is NULL then both functions returns null.
If the string is having
trailing blanks then LEN()
function returns the length only up to the last non null character and
ignores the spaces.
On the other hand, DATALENGTH() behaves differently
returning all along with spaces.
Example:
DECLARE @value1
VARCHAR(25)
SELECT @value1 = 'Reddyz ' SELECT @value1 AS COL_TEXT, LEN(@value1) AS LENGTH, DATALENGTH(@value1) AS DATA_LENGTH
The result would be 6 and 11.
As DATALENTH() takes into account the spaces , so it returns 11 and
LEN() returns only the non-null characters so it returns
6.
|
The Blog for Professional Learning of the SQL Server,T-SQL,SSIS,Power BI,R Programming and Excel VBA Macros
Tuesday, 5 November 2013
What is the Difference between Len() and DataLength() Functions in SQL
Subscribe to:
Post Comments (Atom)
ExcelKingdom-Popular Posts
-
How to use Dim and Redim Statements in VBA Dim Statement: Variables declared with Dim at the script level are available to all proced...
-
Excel VBA to Get Combo box or Dropdown Index Value Suppose we have a Form Control Combobox or Dropdown lis as follows : Now we can ...
-
Excel VBA Macro to Find the Count of Vowels and Consonants in a String Suppose we have a String " ExcEl kIngdOm fOr ExcEl vbA mAcrOs&...
-
Excel VBA Macro to Apply Border Around or Outsider Border to the Selection The following simple macros shows the setting borders to the se...
-
Macro to Select the First Visible Cell in a Column in a Filtered Range Sub Slect_First_Visible_Cell_In_Filtered_Range() Dim Rng As Varian...
-
Excel VBA Macro to Export or Save Each Sheet as Tab Delimited Text File with Back Up 'This Macro Creates a Folder With Workbook Name ...
-
Excel VBA Macros to Know Current Region , Used Range , Last Row , Last Column and Last Cell -------------------------------------------...
-
Macro To Sort Pivot Table Row Labels, Column Field Labels and Data Values Sub Sort_Pivot_Row_Column_Data() Range("G3").Se...
-
How to Dynamically Create an Excel File with Date Time from SQL Server Table using SSIS Package on each Execution Senario : Suppose I hav...
-
How to Update data in SQL Server Destination Table from Excel Source using SSIS Incremental Load Incremental Load is process of comparing...

No comments:
Post a Comment
Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.