How to remove spaces leaving only one?

Navigation
6

Considering a variable with a text in SQL Server . How can I remove 2 or more spaces within the text? In this case leaving only 1 space.

For example: 

'1 tigre, 2  tigres, 3   tigres'

Should result in: 

'1 tigre, 2 tigres, 3 tigres'
    
asked by anonymous 16.05.2017 / 15:47

1 answer

9

Do this: 

declare @texto varchar(MAX)
set @texto = '1 tigre, 2  tigres, 3   tigres'

set @texto = replace(replace(replace(@texto,' ','<>'),'><',''),'<>',' ')
select @texto

Output:

  

1 tiger, 2 tigers, 3 tigers

Explanation:

  • Change all spaces by <> . replace(@texto,' ','<>') , will result in: 1 , tigers,
16.05.2017 / 15:52
How to round off time? Identify if there is a capital letter in the string