LEN() in SQL Server


In our previous blog, we have discussed the problem and its solution while connecting to SQL Server.

In this blog, we are going to discuss the LEN() function in SQL Server.

LEN(): The LEN() function returns the number of characters required/used to represent an expression.
 
LEN() in SQL Server

Syntax:

LEN( expression )

Things to Remember:

1. LEN() function excludes the trailing blank spaces while calculating the number of characters required/used to represent an expression.
2. The LEN() function return null, if the expression is null.
3. The LEN() function return exact/same number of characters required/used to represent an expression.

Practical Implementation: -

The following example illustrate how to use the function.

I) With ‘varchar’ data type

(i) Finding the LEN of any expression.

declare @varString1 varchar(50)
set @varString1='LIVEVIAR'
select @varString1 as StringValue, LEN(@varString1) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
LIVEVIAR       8

(ii) Finding the LEN of any expression that contain blank spaces.

declare @varString2 varchar(50)
set @varString2='LIVEVIAR    '
select @varString2 as StringValue, LEN(@varString2) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
LIVEVIAR       8

(iii) When expression is null.

declare @varString2 varchar(50)
set @varString2=NULL
select @varString2 as StringValue, LEN(@varString2) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
NULL               NULL


II) With ‘nvarchar’ data type

(i) Finding the LEN of any expression.

declare @varString3 nvarchar(50)
set @varString3='LIVEVIAR'
select @varString3 as StringValue, LEN(@varString3) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
LIVEVIAR       8

(ii) Finding the LEN of any expression that contain blank spaces.

declare @varString4 nvarchar(50)
set @varString4='LIVEVIAR    '
select @varString4 as StringValue, LEN(@varString4) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
LIVEVIAR       8

(iii) When expression is null.

declare @varString4 nvarchar(50)
set @varString4=NULL
select @varString4 as StringValue, LEN(@varString4) as StringLength

Output:

StringValue     StringLength
----------------    -----------------
NULL               NULL

By this, we learn how and where to use LEN () in SQL Server.


For any query, comment us below.


Next - DATALENGTH() in SQL Server

Related Questions: -


Q-1 In this below mentioned SQL Server query, what will be the output for StringStorageSize column?

declare @strValue nvarchar(50);
set @strValue=N'IMAGINATIONHUNT     ';
select @strValue as Value,
LEN(@strValue) as StringStorageSize;

A) 30
B) 31
C) 15
D) 16
Ans. Option (C).
Explanation- The string ‘IMAGINATIONHUNT’ contain 15 character. And LEN() property return number of characters. Therefore, 15.
The output for the following query will be:

Value                           StringStorageSize
---------------------------- ------------------------
IMAGINATIONHUNT 15

Q-2 In this below mentioned SQL Server query, what will be the output for StringStorageSize column?

declare @strValue1 varchar(4);
set @strValue1='IMAGINATIONHUNT     ';
select @strValue1 as Value,
LEN(@strValue1) as StringStorageSize;

A) 30
B) 20
C) 15
D) 4
Ans. Option (D).
Explanation- As per the query we have to undergo certain keys:
(i) the variable ‘@strValue1’ is of ‘varchar’ data type, which fall under Character Strings Data Type category.
(ii) Length is 4. [because the value of n=4 in varchar(4)].
(iii) LEN() function return the first four characters used i.e., 4.
The output for the following query will be:

Value       StringStorageSize
---------     ------------------------
IMAG      4




Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS

Keep learning and sharing...

Comments

Popular posts from this blog

SSC CGL 2016 Tier-1 Marks / Result

DATA TYPES IN C#

Pattern printing program

IS STRING MUTABLE OR IMMUTABLE IN .NET?

Top Mutual Funds

SSC CGL 2017 TIER 2 RESULT OUT

JUST IN TIME (JIT)

Can we have multiple Main methods in one class

TOP 15 VISUAL STUDIO SHORTCUT KEYS

Top 10 Interview Tips and Questions