1

I want to delete a certain string in a cell in MS Access, whether in a column or in the whole table. I know that I can do this using plain old Find and Replace but in this case it is not economical for my thousand-row table.

For example,

  • remove all Unknown values from all columns.
  • remove the string "dollars" from the values in column price ie. if the cell contains "34 dollars", it will just be "34".

Can this be done in SQL and how?

  • An UPDATE query is the way to modify table contents (if thats what you mean by *cells*); http://office.microsoft.com/en-gb/access-help/update-data-by-using-a-query-HA010076527.aspx – Alex K. Jan 14 '14 at 16:05
  • 1
    There are a range of string manipulation function which can be used in Access: http://www.techonthenet.com/access/functions/ and http://www.quepublishing.com/articles/article.aspx?p=1143872&seqNum=6 are useful resources to get you started. It will help your chances of getting assistance if you provide an example of your attempts. – Matt Weller Jan 14 '14 at 16:13

1 Answers1

1

Assuming your query will run within an Access session, the second goal is easy. You can Replace dollars with a zero-length string.

UPDATE YourTable
SET price = Replace(price, 'dollars', '');

You could use the same strategy for the first goal, but may decide it makes more sense to examine the datatypes of the table's fields and only UPDATE those which are text or memo.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Is it possible to change `price` into something that will represent all the fields regardless of their type? –  Jan 14 '14 at 18:05
  • I don't understand that question. Please try again. – HansUp Jan 14 '14 at 18:06
  • Thanks. All of my fields have the value `Unknown` in one of the rows, I am trying to change this value into a `''` since Find and Replace takes time to do it. –  Jan 14 '14 at 18:10
  • If you know the name of a field which might contain *Unknown*, you can execute an `UPDATE` like `UPDATE YourTable SET price = Replace(YourFieldName, 'Unknown', '');` However, Access SQL does not support a generalized method which says *"look in every field in this table and substitute a zero-length string for 'Unknown'".* Is that what you're asking for? (Sorry, I'm puzzled.) – HansUp Jan 14 '14 at 18:15
  • Yes, it's the latter I was asking for. Thank you for all the help! –  Jan 14 '14 at 18:21