Eliminating Blank Spaces in SQL Server Tables

Eliminating Blank Spaces in SQL Server Tables

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Blank spaces always needed special handling in many programming languages.

This is not an exception in the case of the SQL programming language.

In SQL Server, a variety of built-in string functions exist for assisting, among other, the developer to solve data-related problems in the case of undesired blank spaces.

However, the blank spaces scenarios vary and sometimes it is necessary to use a combination of various built-in string functions for solving the problem.

For example, consider having one or more columns within a record as the following:

An approach could be the following, using just the REPLACE built-in function like this:

Also, you could try this:

The above two approaches do not fully solve the problem of the specific blank spaces scenario.

As you can see, it started getting a little bit tricky regarding how it is possible to efficiently handle the specific blank spaces problem.

Further below I am suggesting a workaround on how to resolve such issues.

To this end, first we need to create a sample data set with a column containing blank spaces:

Now let’s take a look at the data:

OK, it’s time to implement the actual logic for addressing the blank spaces problem.
As it will be too complex to include the logic within a single T-SQL statement, I preferred to create a function because it will be much easier to use it again in the future and the code looks more structured.

Here’s the DDL for my function:

–Function Name: NoBlankSpaces

–Purpose: Eliminate blank spaces (up to three words)
–Author: Artemakis Artemiou
–Input Parameter: Varchar(250)
–Output: Varchar(250)
CREATE FUNCTION [dbo].[NoBlankSpaces]
(
@string varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
declare @part1 as varchar(100)
declare @part2 as varchar(100)
declare @part3 as varchar(100)
declare @part4 as varchar(100)
declare @temp as varchar(100)
set @part1=(rtrim(substring(@string,0,(charindex(‘ ‘,ltrim(@string))))))
set @temp=ltrim(substring(@string,(charindex(‘ ‘,@string)),len(@string)+1))
set @part2=SUBSTRING(@temp,0,CHARINDEX(‘ ‘,@temp))
set @temp=SUBSTRING(@temp,0,250)
set @part3=ltrim(SUBSTRING(@temp,CHARINDEX(‘ ‘,@temp),250))
set @string=rtrim(ltrim(@part1+’ ‘+@part2+’ ‘+@part3))
RETURN @string
END
GO
————-End of Function DDL—————–

As you can see in the above DDL code, I am using a combination of the SUBSTRING, LTRIM, RTRIM and the CHARINDEX SQL Server built-in string functions for achieving my goal.

Now, let’s use the function and see what the outcome will be:

Cool! No more blank spaces! 🙂

The combination of the SQL Server built-in functions worked great towards the solution of the blank spaces issue.

You can download the code I used for this example from here.

I hope you found this post useful.

Drop me a line if you have any questions or comments!

Cheers!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

12 thoughts on “Eliminating Blank Spaces in SQL Server Tables

  1. Phil Factor

    Wouldn't it be simpler just to do this?

    DECLARE @trimmed VARCHAR(255) ,
    @LastTrimmed INT
    SELECT @trimmed = 'this has too many spaces' ,
    @LastTrimmed = 0

    WHILE LEN(@Trimmed) <> @LastTrimmed
    SELECT @LastTrimmed = LEN(@Trimmed) ,
    @trimmed = REPLACE(@trimmed, ' ', ' ')
    SELECT @Trimmed

  2. Artemakis Artemiou [MVP]

    Hi Phil,

    Thank you for your comment.

    It is always great to hear from fellow community members.

    Your suggestion is very good. Of course you are using a WHILE loop which can be a little bit more expensive from a performance perspective but it is simpler.

    Please note that if you try the following it won't work:

    Image Link 1

    Though, if you try this, yes it will work (add one more space in the REPLACE function parameter):

    Image Link 2

    And to be more correct and handle any preceding or following blank spaces, you can include to your code LTRIM and RTRIM:

    Image Link 3

    Cheers,
    Artemakis

  3. Phil Factor

    Somehow, the posting operation took out the second space from the first ' ' in the REPLACE function.(the second ' ' should have one space in it. It may be better to do them as Char(32)+Char(32), Char(32). It was cut/pasted straight from working code, but I couldn't edit it subsequently.

    The Ltrim/Rtrim operation would be handy in some circumstances.

  4. Alexandre

    Hi Artemakis,
    the first came to my mind is actually similar to Phil's answer but more a little bit more concise

    Solution 1:
    while charindex('  ', @text) > 0 set @text = replace(@text, '  ', ' ')

    where variable @text contains the string you wont to trim

    declare @text varchar(255)
    set @text = 'John    F.          Clark'

    Note: Use rtrim and ltrim to remove trailing spaces for Solution 1

    I agree with you, such a solution might take longer if you have a lot of spaces inside — in such cases you can use Solution 2.

    Solution 2:
    declare @index int, @temp varchar(255)
    select @temp = '', @index = charindex(' ', @text)
    while @index > 0
    begin
      set @temp = @temp + substring(@text, 1, @index)
      set @text = ltrim(substring(@text, @index + 1, len(@text) – @index))
      set @index = charindex(' ', @text)
    end
    set @text = @temp + @text

  5. Artemakis Artemiou [MVP]

    Hi Alexandre,

    Thank you for your comment.

    Yes, that's very correct. Your suggestion works for many (if not all) scenarios.

    As we see there are many solutions to this issue, the important thing is to first analyze each case and decide which one of the available solutions should be used.

    Cheers,
    Artemakis

  6. Phil Factor

    The simple version I've just added is safe for blocks of spaces up to 208 characters long, which should suffice. The huge advantage is its speed, as it requires no UDF to clean up text with space in it. uoi can addt LTRIM and RTRIM to taste

  7. Phil Factor

    SELECT
    REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (
    'this has too many spaces' ,
    REPLICATE(CHAR(32), 6),CHAR(32)),
    REPLICATE(CHAR(32), 5), CHAR(32)),
    REPLICATE(CHAR(32), 4), CHAR(32)),
    REPLICATE(CHAR(32), 3), CHAR(32)),
    REPLICATE(CHAR(32), 2), CHAR(32))

    /* This version is safe for blocks of spaces up to 208 characters long, (38 if you miss out the replacement for six consecutive spaces, and 10 if you miss out both the five and six consecutive spaces) which should suffice.
    The huge advantage is its speed, as it requires no UDF to clean up text with space in it.

  8. Artemakis Artemiou [MVP]

    Hi Phil,

    I agree, this can be a solution too, and a fast one!

    As I said in my previous comment, finally it depends on the case when it comes to which solution to use.

    The good thing is that there are many solutions for different scenarios 🙂

    Cheers,
    Artemakis