Remove Duplicate Space in Middle of String

Question

Hello, I need to remove duplicate spaces in a string and leave only 1, identico a funcao arrumar do Excel. How to do this in sql?

Answer 1

Basic Concepts

Trim: Remove all whitespace independent of position (Left or Right) from the desired field.
Ltrim: Remove the white spaces that are to the left of the desired field.
Rtrim: Removes whitespace to the right of the desired field.

Examples:
1 - How to remove excess whitespace from the following text:

'  Papo Sql - Retirando espaços em Branco  '
 Select Trim(' Papo Sql - Retirando espaços em Branco ');  
 Results  
 'Papo Sql - Retirando espaços em Branco' 

Note that, the spaces on the right and left were eliminated, let’s see the next situation.

2 - How to remove excess whitespace from the following text:

'  Papo Sql - Retirando espaços em Branco à esquerda de um campo  '
 Select LTrim('  Papo Sql - Retirando espaços em Branco a esquerda de um campo  ');  
 Results  
 'Papo Sql - Retirando espaços em Branco a esquerda de um campo   '  

In this case, we still have spaces to the right, however, the function fulfilled with the proposed, remove the spaces to the left, now:

3 - How to remove excess whitespace from the following text:

'  Papo Sql - Retirando espaços em Branco à direita de um campo  '
 Select RTrim(' Papo Sql - Retirando espaços em Branco a direita de um campo '); 
 Results  
 ' Papo Sql - Retirando espaços em Branco a direita de um campo' 

This time, we have the reverse of the previous situation, removing the spaces on the right.

Returning to the initial subject, when you are going to enter the information in the database, use the functions, thus preventing text type fields have unnecessary spaces.

Now, when the bank already contains blank spaces, the deletion should occur with the use of Update, using Trim, Ltrim or Rtrim, depending on your situation, let’s take the example:

 Update tabela  
 Set nome = Trim(nome); 

In this example we remove all spaces to the right and left of the name column.

SOURCE