Eliminating Blank Spaces in SQL Server Tables

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. Yes, sometimes such things happen 🙂

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

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

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

8. Hi Phil,

   Unfortunately I cannot see the newly added code on the blog, I just received it via an email notification. Can you please repost it?

   Thanks.

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

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

11. how to find substring in a string
    e.g raw material
    in this string i want to extract First letter of raw n first letter of material….

12. Hi priyanka,

    Check out my latest article (added today) – Requirement 3.

    Cheers,
    Artemakis

Comments are closed.