delete a record contains `%`

Question

I want to delete a record which contains % sign, for example:

white%House, Den%mark, bengali%20Tiger, White%202.

I have this query but it doesn't work for me.

delete from nail where name like '%[%]%'

I am using C# and Access.

http://stackoverflow.com/questions/439495/tsql-like-escape-clause — Davide Piras, Feb 14 '11 at 17:04

score 1 · Answer 1 · edited Feb 23 '11 at 15:55

1

I think there is some confusion going on, so here is a complete code example that works for me:

using ( var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\users\john\desktop\Database11.accdb;Persist Security Info=False;"))
{
     conn.Open();
     OleDbCommand cmd = 
         new OleDbCommand("DELETE FROM TABLE1 where [name] like '%[%]%'", conn);
     cmd.ExecuteNonQuery();

     cmd.Dispose();
     conn.Close();
}

Note that you are using "name" as one of your fields, which is a reserved word, so you need to escape it inside of brackets. The same goes for the %, since it is the wildcard character that is used by OLEDb.

edited Feb 23 '11 at 15:55

BIBD

15,107
25
85
137

answered Feb 14 '11 at 17:04

John Koerner

37,428
8
84
134

They support both. `%` is the "official" SQL [wildcard character](http://www.w3schools.com/sql/sql_wildcards.asp). – Uwe Keim Feb 14 '11 at 17:06
i am trying it like this and its not working delete from thumbnail where (((name) ALike \"%[%]%\")) – safi Feb 15 '11 at 08:45
I modified the code example to be a full example and changed it to use the correct wildcard values, based on Uwe's suggestion. – John Koerner Feb 15 '11 at 12:56

score 1 · Answer 2 · answered Feb 14 '11 at 17:06

1

You can use InStr() to find if the name contains %

where InStr(name, '%') > 0

answered Feb 14 '11 at 17:06

Raj Kashyap

81
4

It may work for the specific situation, but it doesn't generalise well. The @safi really is looking for how to escape the % character. – BIBD Feb 14 '11 at 19:14
@CodeSlave, I really don't know Access... I would like to understand why `ALike "%[%]%"` is better than `InStr(name, '%')`, if you don't mind... – Raj Kashyap Feb 14 '11 at 20:21
1

No problem. Lets say you wanted to delete all row starting with `foo%`; if you were to do the same trick: `InStr(name 'foo%'') > 0`, you could be deleting other rows: `barfoo%` and `yaddahfoo%`. With `Like "foo[%]%"` you only delete `foo%`, `foo%bar`, `foo%foo`, etc. Escaping the % properly lets you continue to use the `Like` condition (ALike in this case seems to be Access/Jet's ANSI-92 version of the Like condition). Your method works, but kind of kludgey. – BIBD Feb 14 '11 at 22:11

score 1 · Answer 3 · answered Feb 23 '11 at 21:48

For completeness sake:

Within MS Access running in its default SQL mode (ANSI 89), you would use:

  Like "*%*"

Likewise, if you're using DAO to execute the SQL, you'd use that.

You only run into problems here when running in ANSI 92 mode, which is available within Access only if you have ANSI 92 turned on, or if you're using ADO, or if you use ALIKE instead of LIKE. In that case, you have to use the more standard % wildcard. If you're running within Access or using DAO, you can use ALIKE to allow you to use the % wildcard:

ALike "%[%]%"

But if you're running Access in SQL 92 mode, you don't need ALIKE at all, you can just use LIKE (but I would never recommend running Access in SQL 92 mode, as it breaks a number of things within Access).

If you're using ADO/OLEDB to execute the SQL, your only option is SQL 92 mode, and you should be able to use either ALike "%[%]%" or Like "%[%]%". I don't know whether the double quotes make a difference or not, though. In my tests using ADO within Access, it didn't make any difference.

score 0 · Answer 4 · answered Feb 14 '11 at 17:10

0

try:

delete from nail where name like '%[[%]%'

answered Feb 14 '11 at 17:10

alexl

6,841
3
24
29

BIBD · Accepted Answer · 2011-02-14T19:18:58.770

0

The following worked for me native in MS Access

DELETE foo.foo1
FROM foo
WHERE (((foo.foo1) ALike "%[%]%"));

Maybe it's the double quotes instead of the apostrophes.

Perhaps in C# you need to do something like

bar = "DELETE foo.foo1 FROM foo WHERE (((foo.foo1) ALike \"%[%]%\"));";

edited Feb 14 '11 at 19:18

answered Feb 14 '11 at 19:11

BIBD

15,107
25
85
137

delete from thumbnail t where (((t.name) ALike \"%[%]%\")), this is how i did it and still it is not working; – safi Feb 15 '11 at 08:42
What does it complain about? The "alike"? Have you tried flipping it back to "like"? – BIBD Feb 15 '11 at 15:31

delete a record contains `%`

5 Answers5