SQL query to replace all occurrences of space in a table with underscore

Question

How can I write a SQL query to replace all occurrences of space in a table with underscore without writing individual statements for each column?

Answer 1

UPDATE [table_name] SET [field_name] = replace([field_name],' ','_'), [field_name2] = replace([field_name2],' ','_')

However, this way you still have to sum up every column, so above answer might fit more your need

Answer 2

You can use a mixture of VBA and SQL:

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM TheTable")

For i = 0 To rs.Fields.Count - 1
   ''You may wish to check dbMemo as well as dbText
   If rs.Fields(i).Type = dbText Then
      ''You have to watch out for single quotes as well as spaces
      s = "UPDATE TheTable SET [" & rs.Fields(i).Name & "]  = " _
      & "Replace(Replace([" & rs.Fields(i).Name & "],""''"",""'""),"" "",""_"") " _
      & "WHERE Instr([" & rs.Fields(i).Name & "],"" "")>0 " _
      & "AND [" & rs.Fields(i).Name & "] Is Not Null"
      db.Execute s, dbFailOnError
      Debug.Print db.RecordsAffected
    End If
Next

Answer 3

Sometimes you don't need a hammer (code) even if the problem looks like a nail.

If this is a one-off task and the table isn't more than a few million rows you can just open the table and do a find and replace from the Edit menu (or ctrl-h) in Access.

This is one of those handy data manipulation capabilities of Access that make it so darn useful for ad-hoc database work.

Caveats:
(1) Performance won't be great on large tables, but works well for one-off data cleanup tasks in moderate sized tables (or when you are willing to wait a few minutes for it to finish on large tables.
(2) The locks this technique will create are a bit oppressive, so this isn't advised for a DB that is in active use by a large number of users.