How to trim a string in SQL Server before 2017?
To Trim on the right, use:
SELECT RTRIM(Names) FROM Customer
To Trim on the left, use:
SELECT LTRIM(Names) FROM Customer
To Trim on the both sides, use:
SELECT LTRIM(RTRIM(Names)) FROM Customer
I assume this is a one-off data scrubbing exercise. Once done, ensure you add database constraints to prevent bad data in the future e.g.
ALTER TABLE Customer ADD CONSTRAINT customer_names__whitespace CHECK ( Names NOT LIKE ' %' AND Names NOT LIKE '% ' AND Names NOT LIKE '% %' );
Also consider disallowing other characters (tab, carriage return, line feed, etc) that may cause problems.
It may also be a good time to split those Names into family_name
, first_name
, etc :)