Eliminating Blank Spaces in SQL Server Tables

In this article, we will be discussing about eliminating blank spaces in SQL Server table records and columns.

 

About Blank Spaces

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.

 

Examples of Blank Space Scenarios

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

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

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

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

Also, you could try this:

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

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.

 

How you can Efficiently Handle Blank Spaces in SQL Server

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:

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

 

Now let’s take a look at the data:

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

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:

Eliminating Blank Spaces in SQL Server Tables - Article on SQLNetHub

 

Cool! No more blank spaces! 🙂

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

 

Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Certificate of Completion, downloadable resources and more!)

Learn More

 

Featured Online Courses:

 

Check our Related SQL Server Development Articles:

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub (https://www.sqlnethub.com)

© SQLNetHub

12 thoughts on “Eliminating Blank Spaces in SQL Server Tables”

  1. 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. 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. 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. 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. 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. 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. 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. 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

Comments are closed.