Replacin Spaces in a string

[SuperChick]

I am trying to do a database search on a name that I get from a vb text box. However I'm allowing users to put in a first and last name. In SQL I want the query to include '%John%Doe%'. How do I go about removing the space between John and Doe, and inserting the %?

[jim mcnamara]

    sName = "   John  Doe  "


Function FixIt(str as String) as String
    Dim strName as string
    strName = str
    Do While Instr(strName, "  ") > 0
      strname = Replace(strName,"  "," ")
    Loop
    strName = Replace(strName," ","%")
    FixIt = strName
End Function

[rickm]

Originally posted by SuperChick
I am trying to do a database search on a name that I get from a vb text box. However I'm allowing users to put in a first and last name. In SQL I want the query to include '%John%Doe%'. How do I go about removing the space between John and Doe, and inserting the %?

using vb6:

String = "%" & replace("John Doe"," ","%") & "%"