1

In the table trans I have two such values t_bar and t_pro, and I want to find such values in the table. that start with "t_" So I`m using such query:

select trim(col)
from trans
where trim(col) like "t_%";

No rows are returned. However such query will return desired 2 rows:

select trim(col)
from trans
where trim(col) like "t_***";

What is wrong here? I need to use %, because the real situation more difficult.

Rocketq
  • 5,423
  • 23
  • 75
  • 126
  • `%` has no effect in ms-access. You should use `*` for pattern matching. Hence you get the results with the second query. – Vamsi Prabhala Sep 02 '15 at 13:55
  • 1
    Do you get what you want with `ALike` instead of `Like`? ... `where trim(col) ALike "t_%"` If you want *t* followed by a literal underscore, do it this way: `where trim(col) ALike "t[_]%"` – HansUp Sep 02 '15 at 13:56
  • @HansUp wow, fantastic, it works - but why? – Rocketq Sep 02 '15 at 13:58
  • 2
    `ALike` signals the db engine to expect ANSI wild cards (`%` and `_`) instead of the wild cards Access uses (`*` and `?`) in most situations. Beware that for a query run from ADO/OleDb, Access expects ANSI wildcards in `Like` patterns. – HansUp Sep 02 '15 at 14:03

1 Answers1

2

To match any number of characters that start with t_ you should use,

select trim(col)
from trans
where trim(col) like "t_*"
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58