VBA Function to T-SQL Stored Procedure

[MHansel739]

I am moving a customer's database from Access to SQL using the Upsizing Wizard. We have decided to set it up as an Access Project. I have done all of this, and am now having some issues with some queries. There are two of them - one calls a function RemoveExtraSpaces and the other calls a function FindAndReplace.

The code for Query1 is:

UPDATE [Master Schools] SET [Master Schools].Students_Name = RemoveExtraSpaces([students_name]);

The function for Query1 is:

Function RemoveExtraSpaces(strIn As String) As String
Dim strCurrChar As String
Dim strNewString As String
Dim intSpaceCount As Integer
Dim intLoop As Integer
Dim intLenString As Integer
' "2402 NW 26 St" becomes "2402 NW 26 ST"
' Removes all but one space

intLenString = Len(strIn)

For intLoop = 1 To intLenString
strCurrChar = Mid(strIn, intLoop, 1)
If strCurrChar = " " Then
intSpaceCount = intSpaceCount + 1
Else
intSpaceCount = 0
End If
If intSpaceCount < 2 Then strNewString = strNewString & strCurrChar
Next intLoop

RemoveExtraSpaces = Trim(strNewString)

End Function


The code for Query2 is:

UPDATE [Master Schools] SET [Master Schools].Students_Name = FindAndReplace([students_name]);


The function for Query2 is:

''************ Code Start **********
'This code was originally written by Alden Streeter.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Alden Streeter
'
Function FindAndReplace(ByVal strInString As String) As String
strFindString = &quot; , &quot;
strReplaceString = &quot;, &quot;
Dim intPtr As Integer
If Len(strFindString) > 0 Then 'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
strReplaceString
strInString = Mid(strInString, intPtr + Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function
'************ Code End **********

Now, this worked just fine with an Access Database, but it WILL NOT work with an Access Project. How would I rewrite these functions in T-SQL? And, can I combine them somehow into one T-SQL procedure?

Thanks in advance.

--Matthew Hansel