How to remove the space(more the two space) between two word in SQL

Question

declare @var nvarchar(500)='Software    Development'
select @var as NotTrim
select LTRIM(RTRIM(@var)) as Trim

Expecting output is "Software Development"(With single between two words)

Answer 1

Try the following

SELECT
  s,
  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(s,' ',' ^'),'^ ',''),'^','')))
FROM (SELECT '   Q  W       E   ' s) q

^ - you can use any symbol which isn't contained in your string. This method I found on forum sql.ru.

This method replaces any count of spaces to one space. I used this method many times and I think it works well.

Your example

declare @var nvarchar(500)='Software    Development'
select @var as NotTrim
select LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@var,' ',' ^'),'^ ',''),'^',''))) as Trim

You can see how it works step by step

SELECT
  s,
  -- step 1 - add special char before to each spaces
  REPLACE(s,' ',' ^'),
  -- step 2 - delete special char and space after this special char
  REPLACE(REPLACE(s,' ',' ^'),'^ ',''),
  -- step 3 - delete special chars
  REPLACE(REPLACE(REPLACE(s,' ',' ^'),'^ ',''),'^',''),
  -- step 4 - delete start and end spaces if you need it
  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(s,' ',' ^'),'^ ',''),'^','')))
FROM (SELECT '   Q  W       E   ' s) q

Answer 2

Try this,

DECLARE @var NVARCHAR(500)='Software              Development'
SELECT @var AS NotTrim
SELECT Trimmed = REPLACE(REPLACE(REPLACE(@var,' ','<>'),'><',''),'<>',' ')

Hope this helps.