Today I was looking at a table with an apparantly empty column and discovered that the field is actually populated with blank spaces - Fortunately this field is of no interest to me and I noticed it purely by chance when I pasted an extract of the dat into excel and happened to notice the cell was unusually wide when I formatted the sheet. Anyway, it reminded me of an issue that is is often worth looking out for of trailing (or I suppose preceeding) spaces.
Fortunately this is easily solved with the trim function, which will remove white spaces from a field
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename
Depending on the particular 'flavour' of SQL being used:
TRIM() will remove both leading and trailing white spaces;
LTRIM() removes leading white spaces (i.e. from the beginning of a string);
RTRIM() removes trailing white space (i.e. from the end of a string);
If TRIM() is not supported, then LTRIM(RTRIM()) will achieve the same result.
As I said, this is a useful way to get rid of pesky additional spaces, however just because it can be done, it does not necessarily mean it should be done, since additional functions such as this have a cost in terms of performance to the query. One temptation is to apply the function to every field you select in a statement, but this is not a good idea, instead simply bare it in mind as a useful trick for when it is necessary to remove these white spaces.
James