Blank Spaces are Not Including IN Variable

Question

Actually I'm validating the User_Name With the Column user_Name which Present in a table.

User vipul_1 Present in table, But if pass The data, 'vipul_1 ', it Should return 0 rows affected but instead of this return me the Result.

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

Ullas · Answer 1 · 2017-09-25T06:04:25.923

1

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

edited Sep 25 '17 at 06:04

answered Sep 25 '17 at 05:56

Ullas

11,450
4
33
50

Actually I'm validating the User_Name With the Column user_Name Present in A Table.User vipul_1 Present in Table But IF pass The data 'vipul_1 ' It Should return 0 rows affected but instead of this return me the Result.. – Alfaiz Ahmed Sep 25 '17 at 06:03
@AlfaizAhmed : You comment above is totally different from your question. – Ullas Sep 25 '17 at 06:05
@AlfaizAhmed : That means, you want to check the availability of `username` in that table, right? – Ullas Sep 25 '17 at 06:12
Yes whatever I pass In Variable(Might Include blank at the end as I mention) It Should match with Table Column.if not matching it should Return 0 rows Affected. – Alfaiz Ahmed Sep 25 '17 at 06:15
@AlfaizAhmed : For that, why do you want to find the length of the string? – Ullas Sep 25 '17 at 06:39

score 0 · Answer 2 · answered Sep 25 '17 at 06:03

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

Ajay2707 · Answer 3 · 2017-09-25T11:16:19.120

0

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

edited Sep 25 '17 at 11:16

answered Sep 25 '17 at 06:48

Ajay2707

5,690
6
40
58

I'm passing a User 'vipul_1 ' Which is not Present in Table But User 'vipul_1' is presented. Now If I pass 'vipul_1 ' it should not return any Output as no data Present in Table.. – Alfaiz Ahmed Sep 25 '17 at 07:10
I don't wan't to Trim the blank space I wan't it to Get Included in Variable which I'm passing as I Mention Above – Alfaiz Ahmed Sep 25 '17 at 07:12
let me undrestand, if data exist then what you want and if data not exist , then what you want. give me example as output – Ajay2707 Sep 25 '17 at 07:19
Select * from table_User WHERE Name='vipul_1 ' here it will return me 0 rows.because In Table 'vipul_1' is presented – Alfaiz Ahmed Sep 25 '17 at 07:23
that is right and understand, but in this case what you want. – Ajay2707 Sep 25 '17 at 07:25
0 rows should be affected – Alfaiz Ahmed Sep 25 '17 at 07:27
Thanks Buddy But Still find the same I don't to go for Like Operator.it should be = only I need to Validate Password also in same Process – Alfaiz Ahmed Sep 25 '17 at 10:49

Blank Spaces are Not Including IN Variable

3 Answers3