Remove unwanted space between words in a string In SQL SERVER

Many times we are faced lot of difficulties with unwanted space between words, it is easy to remove these types of unwanted spaces in a string using JavaScript or jquery or any server side languages

But here we are discussing about how we can do it in SQL Server

The  simplest solution is given below

DECLARE @example nvarchar(100)

SET @example= '   Secrets   of     programming   '

SELECT  REPLACE(REPLACE(REPLACE(@example,' ','<>'),'><',''),'<>',' ')as [Result]

The above query will result the string like below with out no unwanted spaces between words

Result

---------------------------------

Secrets of programming