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.

Previous - Error while connecting to SQL Server #2

Next - DATALENGTH() in SQL Server

Related Questions: -

#newtoprgm try the first program

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