Remove entire cell if it contians certain string MS-Access 2016

Question

I am trying to delete/replace all cells of a field in MS-Access 2016 which contain a certain string (in my case a "<" & ">").
This would be no problem if only the string itself needed to be deleted/replaces (Remove a string inside a cell in MS Access) but I would like to delete/replace the entire cell if the criteria is met. Example:
Field

<br>I like food
I also like food
Nice to meet you <br>

Result:


I also like food

As you can see, the number of characters as well as the number of words and blanks vary from cell to cell. The only thing they have in common is "<" & ">".

Thank you in advance!

Removing a cell is the same as setting it to the empty string. Does that help? — Hogan, Feb 25 '20 at 16:11
@Hogan But how do I do this if I only want to empty those strings which contain e certain string and keep the others? — Mcgroger, Feb 25 '20 at 16:17
you do an update with a where clause thta defines what cells you want to change. — Hogan, Feb 25 '20 at 16:18
Does it need to be both `<` and `>`? Will you ever have a `<` without a `>` that you want to keep? — Darren Bartrup-Cook, Feb 25 '20 at 16:19

score 2 · Accepted Answer · answered Feb 25 '20 at 16:20

I expect by saying cell you mean the field of a table. So you want to set fields of a table to null if they contain a < and a >.

So this is what you want:

Update YourTable Set YourField = Null Where YourField Like '*<*' And YourField Like '*>*'

If you explicitly want to set the field to an empty string use this:

Update YourTable Set YourField = '' Where YourField Like '*<*' And YourField Like '*>*'

score 1 · Answer 2 · answered Feb 25 '20 at 16:26

This will return records that do not contain a < character.

SELECT MyText
FROM Table1
WHERE INSTR(MyText, "<")=0

this will delete the whole record where MyText contains a <:

DELETE *
FROM Table1 
WHERE INSTR(MyText, "<")>0

Probably better to use the LIKE method that @UnhandledException uses rather than INSTR.

Hogan · Answer 3 · 2020-02-25T16:27:16.613

0

something like this:

update tablename
set cell = ''
where contains(cell,'<') and contains(cell,'>')

if contains is not supported you can use

where cell like '*<*' and cell like '*>*'

edited Feb 25 '20 at 16:27

answered Feb 25 '20 at 16:21

Hogan

69,564
10
76
117

I don't think `contains` is part of MS-Access SQL. – Darren Bartrup-Cook Feb 25 '20 at 16:23
oh good point @DarrenBartrup-Cook -- that is in sql server. Probably have use like as unhandled did. – Hogan Feb 25 '20 at 16:25
Not really down-votable though - no idea who did that. – Darren Bartrup-Cook Feb 25 '20 at 16:27
Not me for sure. I also don't understand that. – AHeyne Feb 25 '20 at 16:27
1

:/ I guess I could delete it, but I don't really care. I got points to spare. – Hogan Feb 25 '20 at 16:28
lol, true. I don't get the time these days to build up a good score (a kill-streak?) :) – Darren Bartrup-Cook Feb 25 '20 at 16:29

Remove entire cell if it contians certain string MS-Access 2016

3 Answers3