[AccessD] Replace multiple spaces with a single space

Efficiency!

Version 1:
SELECT tblElectors.FName, Replace(Replace(Replace(Trim([fname]),"  "," 
"+Chr$(7)),Chr$(7)+" ",""),Chr$(7),"") AS Expr1
FROM tblElectors
WHERE (((Replace(Replace(Replace(Trim([fname]),"  "," "+Chr$(7)),Chr$(7)+" 
",""),Chr$(7),""))<>[fname]));

Version 2:
Function nospaces(str As String) As String
str = Replace(str, "  ", " ")
 If InStr(str, "  ") > 0 Then
     str = nospaces(str)
 End If
 nospaces = str
 End Function

SELECT nospaces([FName]) AS Expr1, tblElectors.FName
FROM tblElectors
WHERE (((nospaces([FName]))<>[fname]));

tblElectors has 4,118,769 records.
There are 6742 problem records.
Version 1 takes 70 seconds on my laptop.
I broke into Version 2 after it had been running for about 15 minutes - Don't know how far it 
had got at that stage. :-(

-- 
Stuart

 

On 16 Nov 2009 at 10:40, Max Wanadoo wrote:

> Why not a straight forward replace function called recursively, viz:
> 
> Option Compare Database
> Option Explicit
> Private str As String
> Function xyz()
>   str = "This          string             has     too       many
> spaces"
>   Call nospaces
> End Function
> 
> 
> Function nospaces() As String
> str = Replace(str, "  ", " ")
> Debug.Print str
> If InStr(str, "  ") > 0 Then
>     Call nospaces
> End If
> End Function
> 
> 
> Max
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: 16 November 2009 10:12
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Replace multiple spaces with a single space
> 
> 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())  
> 
> 
> -- 
> Stuart
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com