[AccessD] Replace multiple spaces with a single space

Have you tried just  swapping those lines around in V2?

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

Moving the "replace"  line after the test should  improve things.

Might be quicker

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 16 November 2009 12:33
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com