remove extra spaces between words (1 Viewer)

[eugzl]

Hi All.
How to create query to remove extra spaces between words in the fields table. I tried like that:

SELECT
LName = TRIM(replace(replace(replace(LName,' ','<>'),'><',''),'<>',' ')),
FName = TRIM(replace(replace(replace(FName,' ','<>'),'><',''),'<>',' ')),
MName = TRIM(replace(replace(replace(MName,' ','<>'),'><',''),'<>',' ')),
Address = TRIM(replace(replace(replace(Address,' ','<>'),'><',''),'<>',' '))
FROM Table1];

and

SELECT
    LName = TRIM(Replace(Replace(LName, '  ', ' '), ' ', ' ')),
    FName = TRIM(Replace(Replace(FName, '  ', ' '), ' ', ' ')),
    MName = TRIM(Replace(Replace(MName, '  ', ' '), ' ', ' ')),
    Address = TRIM(Replace(Replace(Address, '  ', ' '), ' ', ' '))
FROM Table1;[CODE]
Unfortunately it doesn't work.

 

[theDBguy]

Not sure if this will help but take a look here.

Code Snippets

 

[DickyP]

This is how I do it:

Public Function StripMultiSpaces(pstrIn As String) As String
' Ensures only single spaces used.

    Dim re As New VBScript_RegExp_55.RegExp
  
    RE.IgnoreCase = True
    RE.Global = True
    RE.Pattern = "\x20{2,}"

  
    If RE.Test(pstrIn) Then
        StripMultiSpaces = RE.Replace(pstrIn, Space(1))
    Else
        StripMultiSpaces = pstrIn
    End If
  
End Function

Expects a reference to VBScript Regular Expressions.

 

[plog]

Unfortunately it doesn't work.

Unfortunately, that doesn't help us identify the problem. Be more specific--error code? Unexpected results? House catches on fire? How did it "not work".

Looking at your code, I know one way it didn't work--syntactically as SQL. The = in a SELECT statement is only a comparison operator (it tests to see if 2 things are equal). It is not an assignment operator (make this thing equal this). What you most likely want is an alias which uses 'AS' and when you use it you shouldn't use a field that already exists in the underlying data sources. This is an example of how you should do it:

SELECT TRIM(replace(replace(replace(LName,' ','<>'),'><',''),'<>',' ')) AS LNameNoSpaces, ...

 

[GPGeorge]

Unfortunately, that doesn't help us identify the problem. Be more specific--error code? Unexpected results? House catches on fire? How did it "not work".

Spoiler:

Looking at your code, I know one way it didn't work--syntactically as SQL. The = in a SELECT statement is only a comparison operator (it tests to see if 2 things are equal). It is not an assignment operator (make this thing equal this). What you most likely want is an alias which uses 'AS' and when you use it you shouldn't use a field that already exists in the underlying data sources. This is an example of how you should do it:

SELECT TRIM(replace(replace(replace(LName,' ','<>'),'><',''),'<>',' ')) AS LNameNoSpaces, ...

I have to ask. What code did you write that led to a house fire?

 

[CJ_London]

I use a simply loop in a public function which remove any number of consecutive spaces, not just two

function sTrim(s as variant) as variant

    if not isnull(s) then
       sTrim=s
       while instr(sTrim,"  ") '2 spaces
            sTrim = replace(sTrim,"  "," ") ' replace 2 spaces with 1 space
        wend
    end if

end with

 

[tvanstiphout]

Hi All.
How to create query to remove extra spaces between words in the fields table. I tried like that:

SELECT
LName = TRIM(replace(replace(replace(LName,' ','<>'),'><',''),'<>',' ')),
FName = TRIM(replace(replace(replace(FName,' ','<>'),'><',''),'<>',' ')),
MName = TRIM(replace(replace(replace(MName,' ','<>'),'><',''),'<>',' ')),
Address = TRIM(replace(replace(replace(Address,' ','<>'),'><',''),'<>',' '))
FROM Table1];

and

SELECT
    LName = TRIM(Replace(Replace(LName, '  ', ' '), ' ', ' ')),
    FName = TRIM(Replace(Replace(FName, '  ', ' '), ' ', ' ')),
    MName = TRIM(Replace(Replace(MName, '  ', ' '), ' ', ' ')),
    Address = TRIM(Replace(Replace(Address, '  ', ' '), ' ', ' '))
FROM Table1;[CODE]
Unfortunately it doesn't work.

Why are you looking for < and >; those characters do not occur in FN and LN.
Show us the data you are really working with, and what you want it to become after processing.
Piling on: "it doesn't work" is not helpful.

 

[DickyP]

This is how I do it:

Public Function StripMultiSpaces(pstrIn As String) As String
' Ensures only single spaces used.

    Dim re As New VBScript_RegExp_55.RegExp
 
    RE.IgnoreCase = True
    RE.Global = True
    RE.Pattern = "\x20{2,}"

 
    If RE.Test(pstrIn) Then
        StripMultiSpaces = RE.Replace(pstrIn, Space(1))
    Else
        StripMultiSpaces = pstrIn
    End If
 
End Function

Expects a reference to VBScript Regular Expressions.

I don't know if anyone is interested but I also have a enhanced version of this which strip multiples of any character;

Option Compare Database
Option Explicit

Private mre As New VBScript_RegExp_55.RegExp
Private Const cMustEscape As String = ".+*$^\,[]()|?"

Public Function StripMultiChars(pstrList As String, _
                                pstrChar As String) _
                                As String
On Error GoTo Err_StripMultiChars
    Dim strChar As String
    Dim strLocal As String
    
    mre.Multiline = False
    mre.Global = True
    
    strLocal = pstrList
    strChar = Left$(pstrChar, 1)
    If InStr(cMustEscape, strChar) > 0 Then
        mre.Pattern = "\" & strChar & "{2,}"
    Else
        mre.Pattern = strChar & "{2,}"
    End If
    strLocal = mre.Replace(pstrList, strChar)
    StripMultiChars = strLocal
Exit_StripMultiChars:
    Exit Function
Err_StripMultiChars:
    StripMultiChars = pstrList
    Resume Exit_StripMultiChars
End Function

This and the more specific space removal function are highly efficient as this is exactly the sort of task Regular Expressions are designed for.