Blank Spaces are Not Including IN Variable

Returns the number of characters of the specified string expression, excluding trailing blanks. This is clearly mentioned here.

Trailing blanks won't be included in the length count. But it will include the blanks in the beginning of the string.

For example.

DECLARE @User_Name as NVARCHAR(25)
SET @User_Name= 'vipul_1       '
SELECT  LEN(@User_Name)[User_Name]

This will return 7.

But,

DECLARE @User_Name as NVARCHAR(25)
SET @User_Name= '     vipul_1       '
SELECT  LEN(@User_Name)[User_Name];

This will return 12 including the blanks in the beginning.

And if you want to count the trailing spaces also, then just add any character to the end and find the length and subtract 1.

Something like this.

Query

declare @username as varchar(1000);
set @username = 'some_name_here     '; -- you are unknown about the content of string.
set @username = @username + '_'; 
select LEN(@username) - 1 as [name_length];

Find a fiddle demo here

he trailing spaces are there. They are just ignored by the LEN function. Try using DATALENGTH function instead.

DECLARE @User_Name NVARCHAR(25);
SET @User_Name = 'vipul_1       ';
SELECT
    LenVal = LEN(@User_Name),
    DataLenVal = DATALENGTH(@User_Name);

Results...

LenVal      DataLenVal
----------- -----------
7           28

In Sqlserver RTRIM and LTRIM function exist, use it

select LTRIM( ' vipul_1 ') , RTRIM( ' vipul_1  ') , RTRIM(  LTRIM( ' vipul_1 ') ) 

As the latest Sqlserver introduce the Trim function

Updated

SQL Server follows the ANSI/ISO SQL-92 specification, see this below links. For this you compare the string with LIKE operator as suggested below.

Len function is also not work, use DATALENGTH instead of this (As last suggested).

Why the SQL Server ignore the empty space at the end automatically?

https://dba.stackexchange.com/questions/10510/behavior-of-varchar-with-spaces-at-the-end

Declare @table table (name varchar(50))

insert into @table values ('vipul_1'), ('ajay_1') , ('eeee_1') , ('vipul')

DECLARE @User_Name    NVARCHAR(25)
    SET @User_Name= 'vipul_1       '

if Exists (select * from @table where name  = @User_Name and DATALENGTH(name) = DATALENGTH(@User_Name) )
    Select 0
Else
    Select 1

select * from @table where name  like @User_Name
select * from @table where name  = @User_Name
select  * from @table where name  = @User_Name and DATALENGTH(name) = DATALENGTH(@User_Name) --use DataLength for this