Sql Where Clause not working if add space at the end of string

Question

I have below sql Query

Select * from usertable where username = 'xyz (space)';

It is giving me the result which is wrong the result has to come only when i not add space at end because in database there is no space.

Is there any function or how can i do that.

Please give me sample query actualy i don't want the result if there is space at end. — Sharmaji TM, Jun 16 '16 at 07:58
Possible duplicate of [SQL Server 2008 Empty String vs. Space](http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space) — Lasse V. Karlsen, Jun 16 '16 at 08:35
There are several duplicates here on SO regarding this phenomenon: [this](http://stackoverflow.com/questions/1399844/sql-server-2008-empty-string-vs-space), [this](http://stackoverflow.com/questions/3938566/how-can-i-make-sql-server-return-false-for-comparing-varchars-with-and-without-t), [this](http://stackoverflow.com/questions/1143313/why-would-sqlserver-select-statement-select-rows-which-match-and-rows-which-match), etc. — Lasse V. Karlsen, Jun 16 '16 at 08:36

Rhys Jones · Answer 1 · 2016-06-16T09:17:38.617

1

If the data is CHAR data with trailing spaces you could use the RTRIM function like this;

select * from usertable where RTRIM(username) = 'xyz'

EDIT: In order to match including trailing spaces you can use the DATALENGTH function, like this;

select * from usertable where username = 'xyz ' AND
                              DATALENGTH(username) = DATALENGTH('xyz ')

edited Jun 16 '16 at 09:17

answered Jun 16 '16 at 07:55

Rhys Jones

5,348
1
23
44

data is varchar and rtrim will remove space from database but what i want is if there is space at end then result will not come. – Sharmaji TM Jun 16 '16 at 07:57
..thanks its working but change query like below: select * from usertable where username = 'xyz ' AND DATALENGTH(username ) = DATALENGTH('xyz ') – Sharmaji TM Jun 16 '16 at 09:11

Arulkumar · Answer 2 · 2016-06-16T08:26:27.007

I hope you want to filter the records those have the space in the last position.

DECLARE @TestTable TABLE (Data VARCHAR(20));
INSERT INTO @TestTable (Data) VALUES ('xyz'), ('Sharma ');

SELECT * FROM @TestTable WHERE Data = 'Sharma ';
SELECT * FROM @TestTable WHERE Data = 'Sharma';

you will get the same result for WHERE Data = 'Sharma ' and 'Sharma'

Using SUBSTRING(Data, DATALENGTH(Data), 1) you can get the last character of the column and add the condition in WHERE clause will solve your problem:

DECLARE @TestingValue AS VARCHAR(20) = 'Sharma'; -- 'xyz' -- 'Sharma '

SELECT * FROM @TestTable WHERE Data = @TestingValue AND SUBSTRING(@TestingValue, DATALENGTH(@TestingValue), 1) = ' ' ;

The above block return result if you set 'Sharma ' as parameter value.

Actualy i dn't want result if there is space enter by user – Sharmaji TM Jun 16 '16 at 09:09 — Sharmaji TM, Jun 16 '16 at 09:09

score 1 · Answer 3 · answered Jun 16 '16 at 08:34

This is because SQL Server follows ANSI 92 rules which stipulates that:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

(my emphasis)

score 0 · Answer 4 · answered Jun 16 '16 at 08:04

0

If you don't want the result if there is space at end you can use below query

select * from usertable where username not like '% '

answered Jun 16 '16 at 08:04

Malcolm

1,801
3
21
48

score 0 · Answer 5 · answered Jun 16 '16 at 08:08

0

Or this one:

    select * from usertable where right(username,1) <> ' '

answered Jun 16 '16 at 08:08

Djacks007

11
6

score 0 · Answer 6 · answered Jun 16 '16 at 09:14

0

Hi this code works for me........

select * from usertable where username = 'xyz (Space) ' AND
                              DATALENGTH(username) = DATALENGTH('xyz (Space) ')

answered Jun 16 '16 at 09:14

Sharmaji TM

199
1
13

Sql Where Clause not working if add space at the end of string

6 Answers6