SQL problem

hello everyone,

does anybpdy know of the equivalent string function of miscrosoft access 2003's "replace" function in its lower versions??

thanks in advance for any help you can give.

There wasn't one. It had to be coded by you.

Functions :
Instr, Left, Mid, Right


Vince

Quote:

---

Originally posted by Ecniv
There wasn't one. It had to be coded by you.

Functions :
Instr, Left, Mid, Right


Vince

---

is that so? :cry:
anyway thats for the info.
it's greatly appreciated.

TempA: Original String
TempB: The search string ('BLUE') is inside of the TempA text
TempC: The String to replace the found string with if found (i.e. if "blue' is found, replace 'blue' with 'yellow'.
TempD: Will contain the new string with 'blue' turned into yellow.

Code:

---

Public Sub Example()
Dim TempA As String
Dim TempB As String
Dim TempC As String
Dim TempD As String

TempA = "asdfasdfBLUEasdfasd"
TempB = "BLUE"
TempC = "YELLOW"

TempD = Replace(TempA, TempB, TempC)


'.............  TempD will = "asdfasdYELLOWasdfasd"

End Sub
'-------------------------------------------------------------------

Public Function Replace(ByVal vntValue As Variant, ByVal strSource As String, ByVal strDest As Variant) As Variant
'Purpose    Replace part of a string by another.
'Inputs    vntValue    Value where execute substitution
'          strSource  String to replace
'          strDest    String replacement


Dim i, j As Integer
Dim intLngDest As Integer, intLngSource As Integer

    If IsNull(vntValue) Then
        Exit Function
    End If
    If strSource <> strDest Then
        intLngDest = Len(strDest)
        If intLngDest = 0 Then
            intLngDest = 1
        End If
        intLngSource = Len(strSource)
   
        i = 1
        j = InStr(i, vntValue, strSource)
        While j <> 0
            vntValue = Left(vntValue, j - 1) & strDest & Right(vntValue, Len(vntValue) - (j + intLngSource - 1))
            i = j + intLngDest
            j = InStr(i, vntValue, strSource)
        Wend
    End If
    Replace = vntValue
end function

---

Quote:

---

Originally posted by Garratt
TempA: Original String
TempB: The search string ('BLUE') is inside of the TempA text
TempC: The String to replace the found string with if found (i.e. if "blue' is found, replace 'blue' with 'yellow'.
TempD: Will contain the new string with 'blue' turned into yellow.

Code:

---

Public Sub Example()
Dim TempA As String
Dim TempB As String
Dim TempC As String
Dim TempD As String

TempA = "asdfasdfBLUEasdfasd"
TempB = "BLUE"
TempC = "YELLOW"

TempD = Replace(TempA, TempB, TempC)


'.............  TempD will = "asdfasdYELLOWasdfasd"

End Sub
'-------------------------------------------------------------------

Public Function Replace(ByVal vntValue As Variant, ByVal strSource As String, ByVal strDest As Variant) As Variant
'Purpose    Replace part of a string by another.
'Inputs    vntValue    Value where execute substitution
'          strSource  String to replace
'          strDest    String replacement


Dim i, j As Integer
Dim intLngDest As Integer, intLngSource As Integer

    If IsNull(vntValue) Then
        Exit Function
    End If
    If strSource <> strDest Then
        intLngDest = Len(strDest)
        If intLngDest = 0 Then
            intLngDest = 1
        End If
        intLngSource = Len(strSource)
   
        i = 1
        j = InStr(i, vntValue, strSource)
        While j <> 0
            vntValue = Left(vntValue, j - 1) & strDest & Right(vntValue, Len(vntValue) - (j + intLngSource - 1))
            i = j + intLngDest
            j = InStr(i, vntValue, strSource)
        Wend
    End If
    Replace = vntValue
end function

---

---

hey thanks for the reply Garratt..but what i really needed is a replace function inside a query statement..
:confused: