Sql Method to Replace Repeating Blanks With Single Blanks

Replace duplicate spaces with a single space in T-SQL

Even tidier:

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

Output:

select single spaces

SQL method to replace repeating blanks with single blanks

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

replace multiple space with single space in a column

Number of consecutive space characters can either be odd or even. You can replace two space characters with one space character, and do a similar replace again on the modified string to cover all the odd/even cases.

UPDATE abc SET title = REPLACE(REPLACE(title, '  ', ' '), '  ', ' ');

Explanation:

• 2 spaces: First replace will convert to 1 space. Second replace will not modify further.
• 3 spaces: First replace will convert (2+1) spaces to (1+1). Second will convert (1+1 = 2) spaces to 1 space.
• 4 spaces: First replace will convert (2+2) spaces to (1+1). Second will convert (1+1 = 2) spaces to 1 space.

and so on...

---

DEMO:

mysql> select 
    -> dt.test_str, 
    -> REPLACE(REPLACE(dt.test_str, '  ', ' '), '  ', ' ') AS modified
    -> FROM 
    -> (SELECT 'thi   s is  a weird    string' AS test_str) AS dt ;
+--------------------------------+--------------------------+
| test_str                       | modified                 |
+--------------------------------+--------------------------+
| thi   s is  a weird    string | thi s is a weird string |
+--------------------------------+--------------------------+
1 row in set (0.00 sec)

How to replace duplicate cells with blanks/nulls in SQL server 2008?

You can do this using left join and row_number window function.
Please check the columns for correctness before running this query.

SELECT   T2.[Operation cancelled within 28 days]
        ,T2.[Number of new patients seen]
        ,T2.[Number of follow up patients seen]
        ,T2.[Readmission Rate]
        ,T2.[2 Week Target]
        ,T1.[Procedure Split] 
FROM
    (SELECT DISTINCT [Operation cancelled within 28 days]
                     ,[Procedure Split] 
                     ,ROW_NUMBER() OVER(ORDER BY NUM) AS RN FROM Your_table) T1

 LEFT JOIN 

    (SELECT * FROM (SELECT DISTINCT  [Operation cancelled within 28 days]
                                    ,[Number of new patients seen]
                                    ,[Number of follow up patients seen]
                                    ,[Readmission Rate]
                                    ,[2 Week Target]
                                    ,ROW_NUMBER() OVER(ORDER BY A) AS RN FROM Your_table) X 
    WHERE RN=1 ) T2
ON T1.RN = T2.RN

How to replace multiple white spaces and carriage return with a single white space in sql

Well I'll just put this out there as an alternative since I just finished it up the second an answer was accepted.

This will also give you the results you desired by trimming and replacing in the correct orders:

Select Replace(replace(replace(replace(
                                       RTRIM(LTRIM(this)), 
                                       char(13) + char(10), ''),
                                       '  ', ' |'),
                                       '| ', ''),
                                       '|','')
from
(select '      6      Spaces  6      Spaces.     
            abcde ' as this) a

Remove multiple blanks with a single blank in Spark SQL

Even better that I have now been enlightened by a real expert. It's simpler in fact:

   import org.apache.spark.sql.functions._
// val myUDf = udf((s:String) => Array(s.trim.replaceAll(" +", " ")))
   val myUDf = udf((s:String) => s.trim.replaceAll("\\s+", " ")) // <-- no Array(...)
// Then there is no need to play with columns excessively:

   val data = List("i  like    cheese", "  the dog runs   ", "text111111   text2222222")
   val df = data.toDF("val")
   df.show()

   val new_df = df.withColumn("new_val", myUDf(col("val")))
   new_df.show

---

Related Topics