Taming the Whitespace: Multiple Ways to Trim Strings in SQL Server (Before 2017)

These functions remove leading and trailing spaces (or other specified characters) from a string, respectively. Here's an example:

-- Sample data
DECLARE @name NVARCHAR(50) = '  foo   ';

-- Combining LTRIM and RTRIM for trimming
SELECT Name = RTRIM(LTRIM(@name)) AS TrimmedName;

This code first removes leading spaces using LTRIM and then removes trailing spaces using RTRIM, effectively trimming the entire string.

Using SUBSTRING and LEN functions:

This approach leverages the string manipulation capabilities of SUBSTRING and LEN functions. Here's how it works:

-- Sample data
DECLARE @text NVARCHAR(50) = '   Hello, world!   ';

-- Trimming using SUBSTRING and LEN
SELECT TrimmedText = SUBSTRING(@text, LEN(LTRIM(@text)) + 1, LEN(@text) - LEN(RTRIM(@text)))

1. LEN(LTRIM(@text)) finds the length of the string after removing leading spaces.
2. SUBSTRING extracts the desired portion of the string, starting from the first non-space character (index LEN(LTRIM(@text)) + 1) and spanning the remaining characters (length calculated in step 2).

Using REPLACE function (caution advised):

While not ideal for all scenarios, the REPLACE function can be used to remove all spaces from a string. However, it's important to be cautious as this approach removes all spaces, not just leading and trailing ones.

-- Sample data
DECLARE @message NVARCHAR(100) = 'This string has spaces.';

-- Trimming using REPLACE (removes all spaces)
SELECT TrimmedMessage = REPLACE(@message, ' ', '')

This code replaces all occurrences of spaces (" ") with an empty string, effectively removing all spaces from the string.

Remember:

• LTRIM and RTRIM are the recommended methods for targeted trimming of leading and trailing spaces.
• SUBSTRING and LEN offer more flexibility for specific trimming needs.
• REPLACE should be used with caution, as it removes all spaces, not just leading or trailing ones.