This function removes repeating spaces from a string using a recursive function, leaving only one space between words.
Code:
-- =============================================
-- 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