White spaces in sql

Question

that will sounds stupid, but I have a table with names, those names may finish with white space or may not. E.g. I have name ' dummy ', but even if in the query I write only ' dummy' it will find the record ' dummy '. Can I fix it somehow?

SELECT *
FROM MYTABLE where NAME=' dummy'

Thanks

What DMBS are you using (e.g. MS SQL Server, Oracle, MySQL, PostgresQL, etc)? — JohnLBevan, Oct 26 '16 at 19:33
I have different databases, but currently I am testing on Firebird. So the behaviour could be different for each DB? — Bear Bear, Oct 26 '16 at 19:35
I have mysql, mssql, db2, oracle and firebird for local testing. — Bear Bear, Oct 26 '16 at 19:36
What 's the field definition of `NAME`? I'm guessing fixed length; i.e. `CHAR(n)` as opposed to `VARCHAR(n)`? — JohnLBevan, Oct 26 '16 at 19:37
http://stackoverflow.com/questions/3938566/how-can-i-make-sql-server-return-false-for-comparing-varchars-with-and-without-t — JohnLBevan, Oct 26 '16 at 19:41
you probably want the LTRIM function but it depends on the DB some name it different. — Hogan, Oct 26 '16 at 20:48

dnoeth · Accepted Answer · 2016-10-26T19:47:20.203

1

This is how SQL works (except Oracle), when you compare two strings the shorter one will be padded with blanks to the length of th 2nd string.

If you really need to consider trailings blanks you can switch to LIKE which doesn't follow that rule:

SELECT *
FROM MYTABLE where NAME LIKE ' dummy'

Of course, you better clean your data during load.

There's only one thing which is worse than trailing spaces, leading spaces (oh, wait a minute, you got them, too).

edited Oct 26 '16 at 19:47

answered Oct 26 '16 at 19:36

dnoeth

59,503
4
39
56

why it is worse to have leading spaces? – Bear Bear Oct 26 '16 at 19:39
#1: Because they are hard to spot, is it one or two or three of them? #2: You need to spcecify the exact number to be able to find them, e.g. `'dummy' <> ' dummy'` – dnoeth Oct 26 '16 at 19:41

White spaces in sql

1 Answers1