One of the most common queries I need to write is to remove rows with spaces only usually caused by bad historical data or trim out the spaces.

A simple view is to show the difference in length when leaving all spaces (first example), removing outer spaces (second example) and removing all spaces (third example).

Please note that I used # around the variables. This is a great way to see spaces which may not look very obvious, especially if it is only one trailing space.


BEGIN TRANSACTION
DECLARE @MyName AS VARCHAR(50)
SET @MyName = ' David Middle Name Hall '

SELECT '#' + @MyName + '#' AS ColumnWithDataAndSpaces,
LEN(@MyName) AS ColumnWithDataAndSpacesLength

SELECT '#' + LTRIM(RTRIM(@MyName)) + '#' AS ColumnWithDataNoOuterSpace,
LEN(LTRIM(RTRIM(@MyName))) AS ColumnWithDataNoOuterSpaceLength

SELECT '#' + REPLACE(@MyName,' ','') + '#' AS ColumnWithData,
LEN(REPLACE(@MyName,' ','')) AS ColumnWithDataLength

ROLLBACK


Another solution is to update the value to NULL if the record is only spaces. This can be done by triming the value and checking the length is less than 1.


BEGIN TRANSACTION

DECLARE @MyName AS VARCHAR(50)

SET @MyName = ' '


SELECT LEN(LTRIM(RTRIM(@MyName))) AS CleanRecordLength,
CASE WHEN LEN(LTRIM(RTRIM(@MyName))) < 1 THEN NULL ELSE LTRIM(RTRIM(@MyName)) END AS CleanRecord

SET @MyName = ' Dave Hall '


SELECT LEN(LTRIM(RTRIM(@MyName))) AS CleanRecordLength,
CASE WHEN LEN(LTRIM(RTRIM(@MyName))) < 1 THEN NULL ELSE LTRIM(RTRIM(@MyName)) END AS CleanRecord

ROLLBACK