Replace duplicate spaces with a single space in T-SQL

Question

I need to ensure that a given field does not have more than one space  I am not concerned about all white space  just space  between characters   So   single    spaces   only    needs to be turned into    single spaces only    The below will not work  select replace  single    spaces   only              as it would result in    single  spaces  only    I would really prefer to stick with native T-SQL rather than a CLR based solution   Thoughts

User · Answer

Found this while digging for an answer   SELECT REPLACE          REPLACE               REPLACE                  LTRIM RTRIM  1 2  3   4    5     6                          CHAR 7            CHAR 7               CHAR 7      AS CleanString where charindex        1 2  3   4    5     6    gt  0   The full answer  with explanation  was pulled from  http   techtipsbysatish blogspot com 2010 08 sql-server-replace-multiple-spaces-with html  On second look  seems to be just a slightly different version of the selected answer

User · Answer

Method  1  The first method is to replace extra spaces between words with an uncommon symbol combination as a temporary marker  Then you can replace the temporary marker symbols using the replace function rather than a loop   Here is a code example that replaces text within a String variable   DECLARE  testString AS VARCHAR 256      Test        text   with  random         spacing  Please normalize  this spacing    SELECT REPLACE REPLACE REPLACE  testString                                       Execution Time Test  1  In ten runs of this replacement method  the average wait time on server replies was 1 7 milliseconds and total execution time was 4 6 milliseconds  Execution Time Test  2  The average wait time on server replies was 1 7 milliseconds and total execution time was 3 7 milliseconds   Method  2  The second method is not quite as elegant as the first  but also gets the job done  This method works by nesting four  or optionally more  replace statements that replace two blank spaces with one blank space   DECLARE  testString AS VARCHAR 256      Test        text   with  random         spacing  Please normalize  this spacing    SELECT REPLACE REPLACE REPLACE REPLACE  testString                                       Execution Time Test  1  In ten runs of this replacement method  the average wait time on server replies was 1 9 milliseconds and total execution time was 3 8 milliseconds  Execution Time Test  2  The average wait time on server replies was 1 8 milliseconds and total execution time was 4 8 milliseconds   Method  3  The third method of replacing extra spaces between words is to use a simple loop  You can do a check on extra spaces in a while loop and then use the replace function to reduce the extra spaces with each iteration of the loop   DECLARE  testString AS VARCHAR 256      Test text with random  spacing  Please normalize this spacing    WHILE CHARINDEX      testString   gt  0 SET  testString   REPLACE  testString            SELECT  testString   Execution Time Test  1  In ten runs of this replacement method  the average wait time on server replies was 1 8 milliseconds and total execution time was 3 4 milliseconds  Execution Time Test  2  The average wait time on server replies was 1 9 milliseconds and total execution time was 2 8 milliseconds

User · Answer

I use FOR XML PATH solution to replace multiple spaces into single space  The idea is to replace spaces with XML tags Then split XML string into string fragments without XML tags Finally concatenating those string values by adding single space characters between two  Here is how final UDF function can be called  select dbo ReplaceMultipleSpaces     Sample   text  with  multiple  space

User · Answer

You can try this   select Regexp Replace  single    spaces   only       2          from dual

User · Answer

I typically use this approach   declare  s varchar 50  set  s    TEST         TEST  select REPLACE REPLACE REPLACE  s       o  c      c  o         o  c

User · Answer

Please Find below code   select trim string agg value       from STRING SPLIT    single    spaces   only         where value lt  gt       This worked for me   Hope this helps

User · Answer

If you know there won t be more than a certain number of spaces in a row  you could just nest the replace   replace replace replace replace myText                                           4 replaces should fix up to 16 consecutive spaces  16  then 8  then 4  then 2  then 1   If it could be significantly longer  then you d have to do something like an in-line function   CREATE FUNCTION strip spaces  str varchar 8000   RETURNS varchar 8000  AS BEGIN      WHILE CHARINDEX        str   gt  0          SET  str   REPLACE  str                  RETURN  str END   Then just do  SELECT dbo strip spaces myText  FROM myTable

User · Answer

This is somewhat brute force  but will work  CREATE FUNCTION stripDoubleSpaces  prmSource varchar max   Returns varchar max  AS  BEGIN     WHILE  PATINDEX          prmSource  gt 0       BEGIN         SET  prmSource   replace  prmSource                  END      RETURN  prmSource END  GO  -- Unit test --  PRINT dbo stripDoubleSpaces  single    spaces   only    single spaces only

User · Answer

Here is a simple function I created for cleaning any spaces before or after  and multiple spaces within a string   It gracefully handles up to about 108 spaces in a single stretch and as many blocks as there are in the string   You can increase that by factors of 8 by adding additional lines with larger chunks of spaces if you need to   It seems to perform quickly and has not caused any problems in spite of it s generalized use in a large application   CREATE FUNCTION  dbo   fnReplaceMultipleSpaces    StrVal AS VARCHAR 4000    RETURNS VARCHAR 4000   AS  BEGIN      SET  StrVal   Ltrim  StrVal      SET  StrVal   Rtrim  StrVal       SET  StrVal   REPLACE  StrVal                            -- 16 spaces     SET  StrVal   REPLACE  StrVal                    -- 8 spaces     SET  StrVal   REPLACE  StrVal                -- 4 spaces     SET  StrVal   REPLACE  StrVal              -- 2 spaces     SET  StrVal   REPLACE  StrVal              -- 2 spaces  for odd leftovers   RETURN  StrVal  END

User · Answer

It can be done recursively via the function  CREATE FUNCTION dbo RemSpaceFromStr  str VARCHAR MAX   RETURNS VARCHAR MAX  AS BEGIN   RETURN  CASE WHEN CHARINDEX        str   gt  0 THEN     dbo RemSpaceFromStr REPLACE  str              ELSE  str END   END  then  for example  SELECT dbo RemSpaceFromStr  some   string    with         many     spaces   AS NewStr  returns  NewStr some string with many spaces  Or the solution based on method described by  agdk26 or  Neil Knight  but safer  both examples return output above  SELECT REPLACE REPLACE REPLACE  some   string    with         many     spaces                  CHAR 7    CHAR 7                    CHAR 7        AS NewStr  --but it remove CHAR 7   Bell  from string if exists     or SELECT REPLACE REPLACE REPLACE  some   string    with         many     spaces                  CHAR 7    CHAR 7    CHAR 7    CHAR 7                    CHAR 7    CHAR 7        AS NewStr --but it remove CHAR 7    CHAR 7  from string  How it works   Caution  Char string used to replace spaces shouldn t exist on begin or end of string and stand alone

User · Answer

update mytable set myfield   replace myfield              where myfield like          Try this

User · Answer

DECLARE  str varchar 150  SET  str  Hello    My   name  is Jiyaul   mustafa  Select REPLACE REPLACE REPLACE  str

User · Answer

This is the solution via multiple replace  which works for any strings  does not need special characters  which are not part of the string    declare  value varchar max  declare  result varchar max  set  value    alpha   beta gamma  delta       xyz   set  result   replace replace replace replace replace replace replace     value  a   ac    x   ab          x    x        x       ab   x    ac   a    select  result --  alpha beta gamma delta xyz

User · Answer

Just Adding Another Method-  Replacing Multiple Spaces with Single Space WITHOUT Using REPLACE in SQL Server-  DECLARE  TestTable AS TABLE input VARCHAR MAX     INSERT INTO  TestTable VALUES   HAPPY         NEWYEAR     2020      WELCOME       ALL           SELECT     CAST   lt r gt  lt   CDATA     input       gt  lt  r gt   AS XML  value    r text    1  cast as xs token    VARCHAR MAX        AS Expected Result FROM  TestTable   --OUTPUT    Expected Result HAPPY NEWYEAR 2020 WELCOME ALL

User · Answer

Even tidier   select string   replace replace replace   select   single       spaces        lt  gt      gt  lt         lt  gt          Output      select single spaces

User · Answer

This would work   declare  test varchar 100  set  test    this   is  a    test   while charindex       test     gt  0 begin    set  test   replace  test             end  select  test

User · Answer

update mytable set myfield   replace  myfield              where charindex       myfield   gt  0    Replace will work on all the double spaces  no need to put in multiple replaces  This is the set-based solution

[sql-server] Replace duplicate spaces with a single space in T-SQL

Examples related to sql-server

Examples related to tsql