# VBForums CodeBank > CodeBank - Other >  SQL Server 2005 - Remove repeating spaces from string using CTE

## CVMichael

This function removes repeating spaces from a string using a recursive function, leaving only one space between words.


```
-- =============================================
-- Author:		<Michael Ciurescu>
-- Create date: <2011-12-27>
-- Description:	<Removes repeating spaces from a string>
-- =============================================
CREATE FUNCTION [dbo].[fn_RemoveSpaces](
	@Data VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @RetStr NVARCHAR(MAX)
	
	-- SELECT dbo.fn_RemoveSpaces(' jlas dflaks djflkj         a;lsdk flask dfla sd      aa    ')
	
	; WITH aa AS (
		SELECT @Data AS data, CAST(1 AS INT) AS i
		
		UNION ALL
		
		SELECT REPLACE(data, '  ',  ' ') data, i + 1
		FROM aa
		WHERE CHARINDEX('  ', data) > 0
	)
	, bb AS (
		SELECT MAX(i) AS MAX_I FROM aa
	)
	SELECT @RetStr = data
	FROM aa
		INNER JOIN bb ON aa.i = bb.MAX_I
	OPTION (MAXRECURSION 0)
	
	RETURN LTRIM(RTRIM(@RetStr))
END
```

----------

