Removing embedded blanks 1

[andre726]

Does anyone know of a reliable method of removing embedded blanks from a database field? Ltrim and Rtrim work well at either end - but how about the ones between "John Smith" and "Mary Brown". This question is applicable for either Foxpro 2.6 or any version of Access.
Thanks.

 

[GingerR]

look up function InStr, Trim, Left, Right, Len (Length).

InStr: search for the first space
Left: take the left of your string up to that first space
then take the right-most portion and trim it.

i suggest making a query, with each part of this operation done separately at first, then put them together when you have what you want.

for example (sub in your field name for NameText):
one column in your select query:
FirstSpace: InStr([NameText]," &quot

then the next column in the select query:
FirstName: Left([Nametext],[FirstSpace]-1)

etc etc, then in the end, put them all together. a fun little project....

 

[chickey]

Alternatively, in Acc2k (I think) there is a function
called 'replace', and used like this:

replace("1 2 3 4 6", " ", "&quot

it may be what you are looking for if you want to do
it all in one function.

Regards...

 

[Norris68]

This should do the job for you - put it in a public module

Public Function SingleSpace(InString As String) As String
Dim Words() As String
Dim A As Long
Dim S As String
Words() = Split(InString, " &quot
S = ""
For A = 0 To UBound(Words())
Words(A) = Trim$(Words(A))
If Words(A) <> &quot;&quot; Then
If S <> &quot;&quot; Then S = S & &quot; &quot;
S = S & Words(A)
End If
Next A
SingleSpace = S
End Function