How to Replace Multiple Characters in Access SQL

SQL Replace multiple different characters in string

You just need to daisy-chain them:

REPLACE(REPLACE(T2.[ShipToCode], '&', 'and'), ',', '')

How to Replace Multiple Characters in SQL?

I would seriously consider making a CLR UDF instead and using regular expressions (both the string and the pattern can be passed in as parameters) to do a complete search and replace for a range of characters. It should easily outperform this SQL UDF.

MS Access SQL: use update and replace to remove multiple spaces with a single one

As mentioned in the comments to the question, the Replace() function does not support regular expressions. However, you could accomplish your goal with the following VBA code:

Option Compare Database
Option Explicit

Sub RemoveMultipleSpaces()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Do While DCount("FieldName", "TableName", "FieldName LIKE ""*  *""") > 0
        cdb.Execute "UPDATE TableName SET FieldName = Replace(FieldName,""  "","" "")"
    Loop
    Set cdb = Nothing
End Sub

edit re: comment

Alternatively, you could use the following code which uses regular expressions to find the replacement candidates:

Option Compare Database
Option Explicit

Public Function RegexReplace( _
        originalText As Variant, _
        regexPattern As String, _
        replaceText As String, _
        Optional GlobalReplace As Boolean = True) As Variant
    Dim rtn As Variant
    Dim objRegExp As Object  ' RegExp

    rtn = originalText
    If Not IsNull(rtn) Then
        Set objRegExp = CreateObject("VBScript.RegExp")
        objRegExp.Pattern = regexPattern
        objRegExp.Global = GlobalReplace
        rtn = objRegExp.Replace(originalText, replaceText)
        Set objRegExp = Nothing
    End If
    RegexReplace = rtn
End Function

Usage example:

RegexReplace("This is     a test.","\s+"," ")

returns

This is a test.

You would use it in a query like this:

UPDATE TableName SET FieldName = RegexReplace(FieldName,'\s+',' ')

Replace characters in Access

"The first two and the last two characters should not be replaced."

Left(col1, 2) will give you the first two characters and Right(col1, 2) will give you the last two.

Seems you want 6 "A" characters in between them; String(6, 'A') will give you that.

Update Table1
set col1 = Left(col1, 2) & String(6, 'A') & Right(col1, 2)
where Table1.col2 = 'R';

If you prefer to use Replace(), adjust the start position and the count of substitutions to perform.

Update Table1
set col1 = Left(col1, 2) & replace(col1, '0', 'A', 3, 6)
where Table1.col2 = 'R';

Use Replace in Access SQL only when the first two characters of the string match my pattern

should be something like that in access

IIF(snametemp like "O *", Replace([snametemp],"O ","O’"), snametemp)

Use replace() function in an update to change more than one sub string of a column

I would just use multiple update statements, but if you absolutely must do it in one statement, just nest the replace calls:

update student set
  name = replace(replace(replace(name, 'one', '1'), 'two', '2'), 'three', '3')

This works because (although inefficient) calls to replace() have no effect if the search term is not found.

Replace accented characters for non-accented characters

To permanently change data, can manually run 60+ operations with Ribbon Find/Replace wizard.

Or build a table that maps these replacement pairs and run VBA procedure like:

Sub FixCharacters()
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblMap")
Do While Not rs.EOF
    db.Execute "UPDATE [tablename] SET [fieldname] = Replace([fieldname], '" & rs!bad & "','" & rs!good & "')"
    rs.MoveNext
Loop
End Sub

---

Related Topics