SQL method to replace repeating blanks with single blanks

by

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.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Get top 1 row of each group
  3. How to cast the DateTime to Time
  4. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  5. Why is SQL Server losing a millisecond?
  6. Find a string by searching all tables in SQL Server
  7. Database Design for Revisions?
  8. Common Table Expression, why semicolon?
  9. T-SQL split string based on delimiter
  10. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  11. How do I find duplicates across multiple columns?
  12. When can I save JSON or XML data in an SQL Table
  13. SQL Server UNION – What is the default ORDER BY Behaviour
  14. Why use a JOIN clause versus a WHERE condition?
  15. How to get last 7 days data from current datetime to last 7 days in sql server
  16. What are projection and selection?
  17. Window Functions or Common Table Expressions: count previous rows within range
  18. Stored procedure that exports data into csv files only exports to one file
  19. Get Start and End Date from Week Number SQL Server
  20. Nested select statement in SQL Server
  21. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  22. Find rows that have the same value on a column in MySQL
  23. Referencing current row in FILTER clause of window function
  24. PostgreSQL 9.3: Dynamic pivot table
  25. How to create a Date in SQL Server given the Day, Month and Year as Integers
  26. Count records for every month in a year
  27. What is the best way to implement soft deletion?
  28. Backup SQL Schema Only?
  29. Postgres: convert single row to multiple rows (unpivot)
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment