SQL method to replace repeating blanks with single blanks

Question

Is there a more elegant way of doing this. I want to replace repeating blanks with single blanks....

   declare @i int

    set @i=0
    while @i <= 20
    begin
        update myTable
        set myTextColumn = replace(myTextColumn, '  ', ' ')
        set @i=@i+1
    end

(its sql server 2000 - but I would prefer generic SQL)

Does this answer your question? [Replace duplicate spaces with a single space in T-SQL](https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql) — Vega, May 14 '22 at 18:07

Paul · Answer 1 · 2010-02-02T09:26:47.147

29

This works:

UPDATE myTable
SET myTextColumn =
    REPLACE(
        REPLACE(
            REPLACE(myTextColumn
                ,'  ',' '+CHAR(1)) -- CHAR(1) is unlikely to appear
        ,CHAR(1)+' ','')
    ,CHAR(1),'')
WHERE myTextColumn LIKE '%  %'

Entirely set-based; no loops.

So we replace any two spaces with an unusual character and a space. If we call the unusual character X, 5 spaces become: ' X X ' and 6 spaces become ' X X X'. Then we replace 'X ' with the empty string. So 5 spaces become ' ' and 6 spaces become ' X'. Then, in case there was an even number of spaces, we remove any remaining 'X's, leaving a single space.

edited Feb 02 '10 at 09:26

answered Feb 02 '10 at 09:21

Paul

16,285
13
41
52

Technically, true. But technically pretty much any SQL will involve a loop. I should have said it uses a single query. – Paul Feb 02 '10 at 09:32
Cleverly avoids looping round the table. I wonder if it could be made any more readable though – cindi Feb 02 '10 at 09:53
If it were me, I'd hide it away in a function. Then you'd have UPDATE myTable SET myTextColumn = dbo.ReduceSpaces(myColumnText) WHERE... and then you can put whatever explanatory text in the function. – Paul Feb 02 '10 at 10:14
But as we all know (I hope), scalar functions cannot be optimised, so we all avoid them (I hope!) – Codesleuth Feb 02 '10 at 13:58
1

hehe...well it depends a bit on what you're using them for. – Paul Feb 02 '10 at 14:17

score 27 · Accepted Answer · edited Sep 09 '16 at 08:06

Here is a simple set based way that will collapse multiple spaces into a single space by applying three replaces.

DECLARE @myTable TABLE (myTextColumn VARCHAR(50))

INSERT INTO @myTable VALUES ('0Space')
INSERT INTO @myTable VALUES (' 1 Spaces 1 Spaces. ')
INSERT INTO @myTable VALUES ('  2  Spaces  2  Spaces.  ')
INSERT INTO @myTable VALUES ('   3   Spaces  3   Spaces.   ')
INSERT INTO @myTable VALUES ('    4    Spaces  4    Spaces.    ')
INSERT INTO @myTable VALUES ('     5     Spaces  5     Spaces.     ')
INSERT INTO @myTable VALUES ('      6      Spaces  6      Spaces.      ')

select replace(
          replace(
             replace(
                LTrim(RTrim(myTextColumn)), ---Trim the field
             '  ',' |'),                    ---Mark double spaces
          '| ',''),                         ---Delete double spaces offset by 1
       '|','')                              ---Tidy up
       AS SingleSpaceTextColumn
 from @myTable

Your Update statement can now be set based:

 update @myTable
    set myTextColumn = replace(
                          replace(
                             replace(
                                LTrim(RTrim(myTextColumn)),
                             '  ',' |'),
                          '| ',''),
                       '|','')

Use an appropriate Where clause to limit the Update to only the rows that have you need to update or maybe have double spaces.

Example:

where 1<=Patindex('%  %', myTextColumn)

I have found an external write up on this method: REPLACE Multiple Spaces with One

I think this is the same as Paul's answer, but your comments make it a bit more readable — cindi, Feb 02 '10 at 10:00
I agree it is better worded and better understandable but somehow it feels like taking credit for someone else's work. Paul's answer is exactly the same. — Lieven Keersmaekers, Feb 02 '10 at 18:47
@Lieven : I made the decision based on reading the external reference Andrew cited above. — cindi, Feb 03 '10 at 09:07
This assumes that your text does not have the symbol '|' in it. This is a very good thread: https://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql — GenDemo, Apr 06 '21 at 00:50

score 6 · Answer 3 · edited Oct 21 '15 at 07:49

6

select 
    string = replace(
                replace(
                    replace(' select   single       spaces',' ','<>')
                    ,'><','')
                ,'<>',' ')

Replace duplicate spaces with a single space in T-SQL

edited Oct 21 '15 at 07:49

shA.t

16,580
5
54
111

answered Jul 19 '15 at 15:08

lynx_74

1,633
18
12

Lieven Keersmaekers · Answer 4 · 2010-02-02T09:25:02.037

3

Not very SET Based but a simple WHILE would do the trick.

CREATE TABLE #myTable (myTextColumn VARCHAR(32))

INSERT INTO #myTable VALUES ('NoSpace')
INSERT INTO #myTable VALUES ('One Space')
INSERT INTO #myTable VALUES ('Two  Spaces')
INSERT INTO #myTable VALUES ('Multiple    Spaces    .')

WHILE EXISTS (SELECT * FROM #myTable WHERE myTextColumn LIKE '%  %')
  UPDATE  #myTable 
  SET     myTextColumn = REPLACE(myTextColumn, '  ', ' ') 
  WHERE   myTextColumn LIKE '%  %'

SELECT * FROM #myTable

DROP TABLE #myTable

edited Feb 02 '10 at 09:25

answered Feb 02 '10 at 09:17

Lieven Keersmaekers

57,207
13
112
146

Of course, this won't work on SQL Server 2000 because of the `OUTPUT` clause, but good answer. – Codesleuth Feb 02 '10 at 09:19
@Codesleuth - thanks, I missed that requirement. The answer has been updated to work with SQL Server 2000. – Lieven Keersmaekers Feb 02 '10 at 09:25
aww, but you changed it to a temp table? That makes me sad, declared tables work in 2000 too you know! – Codesleuth Feb 02 '10 at 09:27
@Codesleuth - lol, no I didn't know. All SQL Server 2000 knowledge is burried in the darkest corners of my head and then some. OP can always take the original version then and just remove the OUTPUT clause. – Lieven Keersmaekers Feb 02 '10 at 09:29

score 3 · Answer 5 · answered Feb 02 '10 at 09:22

3

SELECT 'starting...' --sets @@rowcount
WHILE @@rowcount <> 0
    update myTable
    set myTextColumn = replace(myTextColumn, '  ', ' ')
    where myTextColumn like '%  %'

answered Feb 02 '10 at 09:22

gbn

422,506
82
585
676

cjk · Answer 6 · 2010-02-02T09:30:09.417

Step through the characters one by one, and maintain a record of the previous character. If the current character is a space, and the last character is a space, stuff it.

CREATE FUNCTION [dbo].[fnRemoveExtraSpaces]  (@Number AS varchar(1000))
Returns Varchar(1000)
As
Begin
Declare @n int  -- Length of counter
Declare @old char(1)

Set @n = 1
--Begin Loop of field value
While @n <=Len (@Number)
    BEGIN
     If Substring(@Number, @n, 1) = ' ' AND @old = ' '
      BEGIN
        Select @Number = Stuff( @Number , @n , 1 , '' )
      END
     Else
      BEGIN
       SET @old = Substring(@Number, @n, 1)
       Set @n = @n + 1
      END
    END
Return @number
END
GO


select [dbo].[fnRemoveExtraSpaces]('xxx     xxx     xxx    xxx')

Munavvar · Answer 7 · 2016-09-15T03:59:51.843

1

Here is a Simplest solution :)

    update myTable
    set myTextColumn = replace(replace(replace(LTrim(RTrim(myTextColumn )),' ','<>'),'><',''),'<>',' ')

edited Sep 15 '16 at 03:59

answered May 30 '16 at 05:21

Munavvar

802
1
11
33

score 0 · Answer 8 · answered Feb 02 '10 at 09:15

0

create table blank(
field_blank char(100))

insert into blank values('yyy      yyyy')
insert into blank values('xxxx    xxxx')
insert into blank values ('xxx    xxx')
insert into blank values ('zzzzzz zzzzz')

update blank 
set field_blank = substring(field_blank,1,charindex(' ',field_blank)-1) + ' ' + ltrim(substring(field_blank,charindex(' ',field_blank) + 1,len(field_blank)))
where CHARINDEX (' ' , rtrim(field_blank)) > 1

select * from blank

answered Feb 02 '10 at 09:15

kevchadders

8,335
4
42
61

@ck: I am interested to see loop in sql. Can you please post as update to your answer. tia. – Saar Feb 02 '10 at 09:19

score 0 · Answer 9 · answered Apr 09 '14 at 10:38

For me the above examples almost did a trick but I needed something that was more stable and independent of the table or column or a set number of iterations. So this is my modification from most of the above queries.

CREATE FUNCTION udfReplaceAll 
(
    @OriginalText NVARCHAR(MAX),
    @OldText NVARCHAR(MAX),
    @NewText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    WHILE (@OriginalText LIKE '%' + @OldText + '%')
    BEGIN
        SET @OriginalText = REPLACE(@OriginalText,@OldText,@NewText)
    END

    RETURN @OriginalText
END
GO

score 0 · Answer 10 · edited Mar 12 '16 at 21:32

Lets say, your Data like this

Table name : userdata Field: id, comment, status, 

 id,  "I love -- -- - -spaces  -- -    my INDIA" , "Active" <br>
 id,  "I love -- -- - -spaces  -- -    my INDIA" , "Active" <br>
 id,  "I love -- -- - -spaces  -- -    my INDIA" , "Active" <br>
 id,  "I love -- -- - -spaces  -- -    my INDIA" , "Active" <br>

So just do like this

update userdata set comment=REPLACE(REPLACE(comment," ","-SPACEHERE-"),"-SPACEHERE"," ");

I didn't tested , but i think this will work.

score 0 · Answer 11 · answered Oct 07 '17 at 15:19

0

Try this:

UPDATE Ships
SET name = REPLACE(REPLACE(REPLACE(name, '  ', ' ' + CHAR(1)), CHAR(1) + ' ', ''), CHAR(1), '')
WHERE name LIKE '%  %'

answered Oct 07 '17 at 15:19

besartm

558
1
7
14

score 0 · Answer 12 · answered Aug 02 '18 at 23:00

REPLACE(REPLACE(REPLACE(myTextColumn,' ',' %'),'% ',''),'%','')

Statement above worked terrifically for replacing multiple spaces with a single space. Optionally add LTRIM and RTRIM to remove spaces at the beginning.

Got it from here: http://burnignorance.com/database-tips-and-tricks/remove-multiple-spaces-from-a-string-using-sql-server/

score 0 · Answer 13 · answered Oct 16 '19 at 00:42

0

WHILE
 (SELECT count(myIDcolumn) 
  from myTable where myTextColumn like '%  %') > 0
BEGIN
  UPDATE myTable 
  SET myTextColumn = REPLACE(myTextColumn ,'  ',' ')
END

answered Oct 16 '19 at 00:42

Joshua Madden

1

score -1 · Answer 14 · edited Nov 13 '18 at 13:50

-1

Try it:

CREATE OR REPLACE FUNCTION REM_SPACES (TEXTO VARCHAR(2000))

    RETURNS VARCHAR(2000)

    LANGUAGE SQL

    READS SQL DATA
BEGIN

    SET TEXTO = UPPER(LTRIM(RTRIM(TEXTO)));

    WHILE LOCATE('  ',TEXTO,1) >= 1 DO
        SET TEXTO = REPLACE(TEXTO,'  ',' ');
    END WHILE;

RETURN TEXTO;

END

edited Nov 13 '18 at 13:50

JJJ

32,902
20
89
102

answered Nov 13 '18 at 13:31

Laerte Pequeno

1

score -2 · Answer 15 · edited Oct 21 '15 at 07:51

-2

Update myTable set myTextColumn = replace(myTextColumn, '  ', ' ');

The above query will remove all the double blank spaces with single blank space

But this would work only once.

edited Oct 21 '15 at 07:51

shA.t

16,580
5
54
111

answered Feb 02 '10 at 09:26

Ravi Vanapalli

9,805
3
33
43

@cjk it's clearly mentioned in the answer, it would work only once. – Ravi Vanapalli Oct 21 '15 at 12:59
1

And it is clearly mentioned in the question that the OP wants to replace REPEATING spaces with a single space. The example in the question even has the SQL in it that you posted as an answer, and she has a loop (WHILE) to perform the double-to-single replacement repeatedly. BTW, I did NOT down vote. – Andrew Steitz Sep 09 '16 at 16:55

SQL method to replace repeating blanks with single blanks

15 Answers15

Linked

Related