select '|'+ rtrim(ltrim(replace(replace(replace(replace('   Homer               Simpson  is   kewl ','  ','_'),'__',''),'_ ',' '),'_',' ')))   +'|'

/* Complete functions for creating a test set are listed here below the main function */

/* Create some test data (run once) */
DROP TABLE dbo.AlphaNumericWithSpaces
SELECT * INTO dbo.AlphaNumericWithSpaces 
FROM [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces](1000,50,8,20)

/* Run just to see what the test data looks like */
SELECT * FROM dbo.AlphaNumericWithSpaces AS s
    

/* Since this is an itvf you can use cross apply */

SELECT
    CleanString
FROM 
    dbo.AlphaNumericWithSpaces AS s
CROSS APPLY
    dbo.itvfRemoveDuplicateSpaces(s.CSV) 

/* This is the actual duplicate removal function */

CREATE FUNCTION [dbo].[itvfRemoveDuplicateSpaces]
    (
    @pInputString VARCHAR(8000)
    )
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pInputString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pInputString,t.N,1) = ' ' OR t.N = 0) 
                ),
 cteItem(Item) AS
                (
                SELECT Item = SUBSTRING(@pInputString,s.N1,ISNULL(NULLIF(CHARINDEX(' ',@pInputString,s.N1),0)-s.N1,8000))
   FROM cteStart s
)
SELECT
    STUFF(
    (SELECT 
        COALESCE(NULLIF(' ' + i.Item,' '),NULL) AS [text()] 
    FROM 
        cteItem i
    FOR XML PATH('')),1,1,'')
    AS CleanString
;
GO

  

/* Create the test data function */
/* NOTE: This is the correct function to use for this example. Thanks to Michael Meierruth for spotting the error. */

CREATE FUNCTION [dbo].[CreateCsv8K2DimArrayRandomAlphaNumericWithSpaces]
        (
        @pNumberOfRows            INT,
        @pNumberOfElementsPerRow  INT,
        @pMinElementwidth         INT,
        @pMaxElementWidth         INT
        )
RETURNS TABLE 
     AS
 RETURN

 SELECT TOP (@pNumberOfRows) --Controls the number of rows in the test table
        ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
        CSV =
        (--==== This creates each CSV
         SELECT CAST(
                    STUFF(
                         (
                          SELECT TOP (@pNumberOfElementsPerRow) --Controls the number of CSV elements in each row
                                 ' '
                               + dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')
                               + '|' 
                               + dbo.svfGenerateUniqueCode(@pMinElementwidth,@pMaxElementWidth,' ')
                               + REPLICATE(' ',(ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))/10000000)/10)
                          
                          FROM sys.All_Columns ac3            --Classic cross join pseudo-cursor
                          CROSS JOIN sys.All_Columns ac4       --can produce row sets up 16 million.
                          WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
                             FOR XML PATH('')
                         )
                    ,1,1,'')
                AS VARCHAR(8000))
        )
   FROM sys.All_Columns ac1      --Classic cross join pseudo-cursor
  CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows

GO



/* Used by the test data function to generate random values */

CREATE FUNCTION [dbo].[svfGenerateUniqueCode]
    (
     @CodeMinLength INT
    ,@CodeMaxLength INT
    ,@SpecialChar VARCHAR(50)
    )
RETURNS VARCHAR(100)
AS
BEGIN

    DECLARE @Code VARCHAR(100)

    DECLARE @CodeData TABLE
        (
         CodeChar VARCHAR(1)
        )
        
    DECLARE @Num TABLE
        (
         Digit INT NOT NULL
         PRIMARY KEY CLUSTERED
        )
        
    IF @CodeMaxLength <= @CodeMinLength
        SET @CodeMaxLength = @CodeMinLength + 1


    INSERT INTO @Num
            (
             Digit
            )
            SELECT
                Digit = 0
            UNION ALL
            SELECT
                Digit = 1
            UNION ALL
            SELECT
                Digit = 2
            UNION ALL
            SELECT
                Digit = 3
            UNION ALL
            SELECT
                Digit = 4
            UNION ALL
            SELECT
                Digit = 5
            UNION ALL
            SELECT
                Digit = 6
            UNION ALL
            SELECT
                Digit = 7
            UNION ALL
            SELECT
                Digit = 8
            UNION ALL
            SELECT
                Digit = 9
            ORDER BY
                1

    INSERT INTO @CodeData
            (
             CodeChar
            )
            SELECT
                CodeChar = SUBSTRING(b.Characters,a.RAND_INT % b.MOD,1)
            FROM
                (
                 SELECT
                    aa.Number
                   ,RAND_INT = ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))
                 FROM
                    (
                     SELECT Number = a.Digit + (b.Digit * 10) FROM @Num a CROSS JOIN @Num b
                    ) aa
                ) a
            CROSS JOIN 
                (
                SELECT
                    MOD = LEN(bb.Characters) - 1
                   ,bb.Characters
                FROM
                    (
                     SELECT
                        Characters = 
                        'ABCDEFGHJKLMNPQURSUVWXYZ'
                        + 'abcdefghjkmnpqursuvwxyz'
                        + '0123456789'
                        + @SpecialChar
                    ) bb
               ) b
            ORDER BY
                (SELECT MyNewID FROM dbo.iFunction)

    SELECT
        @Code = ''

    SELECT
        @Code = @Code + CodeChar
    FROM
        @CodeData

    SELECT
        @Code = 
        -- Random length from MIN to MAX Characters
        SUBSTRING(@Code,1,@CodeMinLength + (ABS(CONVERT(INT,CONVERT(VARBINARY(100),(SELECT MyNewID FROM dbo.iFunction))))) % (@CodeMaxLength - @CodeMinLength + 1))

    SET @Code = NULLIF(LTRIM(RTRIM(@Code)),'')

    RETURN @Code

END
GO

CREATE VIEW [dbo].[iFunction] AS 
/**********************************************************************************************************************
 Purpose:
 This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
 a thing directly in the function.  This view also solves the same problem for GETDATE().

 Usage:
 SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
 SELECT MyDate  FROM dbo.iFunction; --Returns a Date

 Revision History:
 Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
 Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code.  No logic changes. 
**********************************************************************************************************************/

SELECT MyNewID = NEWID(),
       MyDate  = GETDATE();

GO

CREATE VIEW [dbo].[iFunction] AS 
/**********************************************************************************************************************
 Purpose:
 This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
 a thing directly in the function.  This view also solves the same problem for GETDATE().

 Usage:
 SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
 SELECT MyDate  FROM dbo.iFunction; --Returns a Date

 Revision History:
 Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
 Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code.  No logic changes. 
**********************************************************************************************************************/

SELECT MyNewID = NEWID(),
       MyDate  = GETDATE();

GO