[AccessD] SPAM-LOW: Replace multiple spaces with a single space

The original article.

http://www.sqlservercentral.com/articles/T-SQL/68378/

I turned this into a UDF since that works for me.

USE [_aDataMaster]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_ReplaceSpace]    Script Date: 11/16/2009 08:16:06 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jeff Moden,
-- Create date: 2009/11/16
-- Description:	http://www.sqlservercentral.com/articles/T-SQL/68378/
-- =============================================
CREATE FUNCTION [dbo].[udf_ReplaceSpace]
(
	-- Add the parameters for the function here
	@OrigString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
	-- Declare the return variable here
--===== Reduce each group of multiple spaces to a single space
      -- for a whole table without functions, loops, or other
      -- forms of slow RBAR.  In the following example, CHAR(7)
      -- is the "unlikely" character that "X" was used for in
      -- the explanation.

	return REPLACE(
		REPLACE(
			REPLACE(
				LTRIM(RTRIM(@OrigString))
                 ,'  ',' '+CHAR(7))  --Changes 2 spaces to the OX model
             ,CHAR(7)+' ','')        --Changes the XO model to nothing
         ,CHAR(7),'')			    --Changes the remaining X's to nothing

END


John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> An rather neat expression to get rid of extra spaces in strings
> i.e.   Convert "This          string             has     too       many      spaces" to
> "This string has to many spaces" 
> 
> Based on an TSQL set based method in the latest SQLServerCentral newsletter.
> Can either be used directly as an expression in a  query or you could create a single line 
> function for it (inline in a query should be more efficient)
> 
> 'uses chr$(7) as a safe substitution character which shouldn't appear in the original
> '1. replaces "space + space" with  "space + Chr$(7)"
> '2. replaces "chr$(7) + space" with an empty strings
> '3. replaces left over chr$(7)s with empty strings
> 
> ReduceSpaces = Replace(Replace(Replace(Trim(OriginalString), "  ", " " _
>                + Chr$(7)), Chr$(7) + " ", ""), Chr$(7), "")
> 
> Can be used almost unchanged in a TSQL stored procedure - just replace Chr$() with 
> CHAR() and Trim() with LTRIM(RTRIM())  
> 
>