Delete record where text field contains special character

[Heidestrand]

Hey community,

I only have a short question: I've a table with several columns, one of them is a text field. Within them I have some records that looks like this and have a special character:

"AX ELEVATE #O SUPERIOR"

As you can see this record contains a special character -> # (hash or pound).

What I want is: I want to delete all records of this table that contain this character. So I tried all variations that are similar to this:

DELETE * FROM tblTest WHERE Description LIKE "*#*"

.. but nothing works. Do you know how to do it?

Thanks in advance! =)

 

[Cronk]

The symbol # is used as a date delimiter.

Try
delete from tblTest where instr(Description,"#")>0

 

[Heidestrand]

Thank you a lot, Cronk! That worked =)

One last question: Is it also possible to use your idea inside the exception part of a query?

For example, when I have something like this:

"...WHERE tblDevices.MaterialID IN (SELECT SNR FROM tblConfig) AND tblDevices.Description NOT LIKE '*#*'"

Can I use your code to ignore records that contain # inside the text field?

 

[arnelgp]

to include a reserved character of MSA in your query enclosed it in square bracket [ ]:

"...WHERE tblDevices.MaterialID IN (SELECT SNR FROM tblConfig) AND tblDevices.Description NOT LIKE '*[#]*'"

your firstpost can be modified like this:

DELETE * FROM tblTest WHERE Description LIKE "*[#]*"

 

[Heidestrand]

Thank you arnelgp, that was a great help! Learned something now today =)
Have a good day