Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, 29 September 2016

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

No comments:

Post a comment

Featured post

Think that makes you rich and richer

 Napolean said: “You can think and grow rich, but if you can be brought up like most people with work and you won't starve, this wil...