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.

Advertisements